I need to open a recordset from a fixed width text file in order to update matching records in a table. The text file has approx 100 records. There are 3 fields:
ID1 9 characters wide
ID2 12 characters wide
Amt 12 characters wide
I can open the recordset with the following code:
When I can do a record count I can see that I have the right number of lines, so I know that the data is there.
Now I need to loop through the data and get the ID1 and ID2 fields in order to match ID1 and update the corresponding ID2 field in my table.
I'm trying to split the line item according to field width, but I don't know how to reference the field name of the merged line. I tried:
But the item is not found in the collection.
Is there a way to reference the 1st field without knowing the field name?
Or, can I split the record into an array somehow?
I've also tried using the Open File for Input method:
That method just gives me one long text string that I can't figure out how to split up into separate records.
Am I close? Is there a better way?
Any help would be greatly appreciated!
Sup
ID1 9 characters wide
ID2 12 characters wide
Amt 12 characters wide
I can open the recordset with the following code:
Code:
Dim DB As DAO.Database
Dim RST As DAO.Recordset
Dim strSQL as string
strSQL = "Select * FROM textfile1.txt "
Set DB = OpenDatabase("C:\TextFiles", False, False, "Text; HDR=Yes")
Set RST = DB.OpenRecordset(strSQL)When I can do a record count I can see that I have the right number of lines, so I know that the data is there.
Now I need to loop through the data and get the ID1 and ID2 fields in order to match ID1 and update the corresponding ID2 field in my table.
I'm trying to split the line item according to field width, but I don't know how to reference the field name of the merged line. I tried:
Code:
Do Until RST.EOF
strID = Trim(Left(RST![*], 9))
strID2 = Trim(Mid(RST![*], 10, 12))
strAmt = Trim(Mid(RST![*], 13, 12))
'do stuff to update records
RST.MoveNext
LoopBut the item is not found in the collection.
Is there a way to reference the 1st field without knowing the field name?
Or, can I split the record into an array somehow?
I've also tried using the Open File for Input method:
Code:
Open "C:\TextFiles\TextFile1.txt" For Input As #1That method just gives me one long text string that I can't figure out how to split up into separate records.
Am I close? Is there a better way?
Any help would be greatly appreciated!
Sup
