Creating a new RECORDSET with .CSV file

[bhuts]

I'm working with a .csv file which has 6 fields and certain number of records.
HOW DO I CREATE A NEW RECORDSET WHICH WILL HOLD ALL OTHER RECORDS EXCEPT THE ONE I RETRIEVE???(on clicking a command button)
I'm using one COMBO BOX and five TEXT BOXES for displaying the retrieved record.
ANY KIND OF HELP APPRECIATED...

 

[Ruairi]

You didn't say what kind of database you are using or how you are accessing it. The recordset open command i used will work for SQL Server or Access using ADO to connect. I assumed you already have an open connection to the database, put that connection variable name wherever you see Your_Connection_Variable. I also assumed there is a table in your database that will hold these 6 fields, i think that is a safe assumption or there would be no reason for you to want to create a recordset out of this .csv file. I may be slightly off on the offsets (the - 1's and + 2's) i'm using for the InStr and Mid calls, I would suggest stepping through this code with watches on the Field1 - Field6 variables and make sure you are getting the whole field into the variables. The BeginTrans method of the connection allows you to update the recordset as you step through the file and then dump all your chages if you dont want to save them to a table. I opened a recordset where the identity column = -1, this will not be the case for any records, so you will get an empty recordset with the fields that you want. hope this helps

Dim FileLine As String 
Dim Field1 As String 
Dim Field2 As String 
Dim Field3 As String 
Dim Field4 As String 
Dim Field5 As String 
Dim Field6 As String 
Dim NextFieldStart As Long
Dim NextFieldEnd As Long 
Dim sql As String 
Dim rs As ADODB.Recordset 

sql = "select NameOfField1, NameOfField2, NameOfField3, "
sql = sql & "NameOfField4, NameOfField5, NameOfField6, " 
sql = sql & "from NameOfTableWithTheseFields where " 
sql = sql & "NameOfIdentityColumn = -1" 
Set rs = New ADODB.Recordset 
rs.Open sql, Your_Connection_Variable, adOpenKeySet, adLockPessimistic adCmdText 

Open "C:\csvfilename.csv" for input as #1 

Your_Connection_Variable.BeginTrans 'Start a transaction
'this allows you to update the recordset when reading the  
'fields, then call the RollBack method if you dont want   
'these records in your table 

While Not EOF(1) 
   Input #1, FileLine 
   NextFieldEnd = InStr(FileLine, ",") - 1
   Field1 = Mid(FileLine, 1, NextFieldEnd) 
   NextFieldStart = NextFieldEnd + 2 'or maybe + 1 
   NextFieldEnd = InStr(NextFieldStart, FileLine, ",") - 1
   Field2 = Mid(FileLine, NextFieldStart, NextFieldEnd - NextFieldStart) 
   NextFieldStart = NextFieldEnd + 2 
   NextFieldEnd = InStr(NextFieldStart, FileLine, ",") - 1
   Field3 = Mid(FileLine, NextFieldStart, NextFieldEnd - NextFieldStart)
   'Get the other 3 fields in the same way 
   
   If <put your criteria here> Then 
       'Fill your textboxes here, it wont add these fields  
       'to the recordset 
   Else 
       rs.AddNew 
       rs(&quot;Field1Name&quot;) = Field1 
       rs(&quot;Field2Name&quot;) = Field2 
       rs(&quot;Field3Name&quot;) = Field3   
       'do the same thing for the next 4 fields
       rs.Update 
   End If 

Wend 

'If you want to add the records you added to the recordset  
'to the table use this line 
Your_Connection_Variable.CommitTrans 
'if you don't want to, use this line 
Your_Connection_Variable.RollBackTrans

Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[swilliams]

bhuts,

To answer the question:
HOW DO I CREATE A NEW RECORDSET WHICH WILL HOLD ALL OTHER RECORDS EXCEPT THE ONE I RETRIEVE???(on clicking a command button)
I would assume that you are selecting the primary key to the records. If so, to open a recordset with all records apart from the one selected, use the SQL WHERE clause:
WHERE Key_Field <> Selection

Or am I missing the point??

Simon