I am writing a program in VBA (within Access 2010) that grabs a specific .txt file, split up the fields as per given specification and imports the values in to an Access table.
So far, I can open the file and see what the program is seeing and I can open the table, but when it comes to writing the values in to the table, I get the following error message:
Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.
I have compared my code with the table, and all fields in the program correctly match up with my table fields.
My code is as folows:
Sub ImportTextFile() Dim LineData As String Dim F1 As String Dim F2 As String Dim F3 As String Dim F4 As String Dim PC As String Dim S As String Dim PN As String Dim F8 As String Dim ED As String Dim F10 As String Dim F11 As String Dim CN As String Dim F13 As String Dim CNAME As String Dim F15 As String Dim REQ As String Dim QTY5 As String Dim QTY10 As String Set cncurrent = CurrentProject.Connection Set rsDiag = New ADODB.Recordset ' Open the text file Open "T:\FileDirectory\File.txt" For Input As #1 ' Open the table to insert the text file into strsql = "Select * from tblTable;" rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic Do While Not EOF(1) ' Read a line of data. Line Input #1, LineData F1 = Left(LineData, 4) F2 = Mid(LineData, 5, 11) F3 = Mid(LineData, 16, 8) F4 = Mid(LineData, 24, 7) PC = Mid(LineData, 31, 4) S = Mid(LineData, 35, 20) PN = Mid(LineData, 55, 2) F8 = Mid(LineData, 57, 7) ED = Mid(LineData, 64, 11) F10 = Mid(LineData, 75, 2) F11 = Mid(LineData, 77, 12) CN = Mid(LineData, 89, 4) F13 = Mid(LineData, 93, 4) CNAME = Mid(LineData, 97, 30) F15 = Mid(LineData, 127, 2) REQ = Mid(LineData, 129, 3) QTY5 = Mid(LineData, 132, 2) QTY10 = Mid(LineData, 134, 2) rsDiag.AddNew rsDiag!ICD9raw = ICDraw rsDiag!Description = ICDDesc rsDiag.Update Loop ' Close the data file. Close #1 rsDiag.Close End Sub
Could anyone please advise on what I could be doing wrong?