Attempting to Import Tab Delimited text file with 274 columns into 2 access tables (1 Viewer)

[curtinch]

I was wonder if anyone could give some insight. I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the help of code I found on an old thread and I'm now trying to accomplish everything with one step. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).

Public Sub ImportTextFile()
' to use the ADODB.Recordset, be sure you have a reference set to ADO
Dim rst As ADODb.Recordset
Dim rst2 As ADODb.Recordset
Dim strFile As String
Dim strInput As String
Dim varSplit As Variant
Dim intCount As Integer
 
Set rst = New ADODb.Recordset
Set rst2 = New ADODb.Recordset
' CHANGE THE TABLE NAME HERE
rst.Open "Importtabledata", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rst2.Open "importtabledata2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
' CHANGE THE TEXT FILE NAME AND LOCATION HERE
strFile = "G:\Home\RiskMgtReports\AutoDatabase\fullextract.txt"
Open strFile For Input As #1
Dim i As Integer
Do Until EOF(1)
' This counter is just to get to the applicable line before importing
intCount = intCount + 1
' reads the text file line by line
Line Input #1, strInput
' starts importing on the second line. Change the number to match which line you
' want to start importing from
If intCount >= 2 Then
' creates a single dimension array using the split function
varSplit = Split(strInput, vbTab, , vbTextCompare)
' adds the record
With rst
.AddNew
For i = 0 To 255
.Fields(i) = varSplit(i)
.Update
Next i
End With
With rst2
.AddNew
.Fields(0) = varSplit(255)
.Fields(1) = varSplit(256)
.Fields(2) = varSplit(257)
.Fields(3) = varSplit(258)
.Fields(4) = varSplit(259)
.Fields(5) = varSplit(260)
.Fields(6) = varSplit(261)
.Fields(7) = varSplit(262)
.Fields(8) = varSplit(263)
.Fields(9) = varSplit(264)
.Fields(10) = varSplit(265)
.Fields(11) = varSplit(266)
.Fields(12) = varSplit(267)
.Fields(13) = varSplit(268)
.Fields(14) = varSplit(269)
.Fields(15) = varSplit(270)
.Fields(16) = varSplit(271)
.Fields(17) = varSplit(272)
.Fields(18) = varSplit(273)
.Update
End With
End If
Loop
' garbage collection
Close #1
rst.Close
Set rst = Nothing
rst2.Close
Set rst2 = Nothing
End Sub

Thank you for any help.

 

[Cronk]

Do you realise that you are trying to import 274 fields?

Is there a problem with data types not matching? The error comes up for which table? I'd test to import say only half the fields and if no error, then 75% or if error 25% to establish which field(s) is problematic.

 

[StarGrabber]

Hi curtinch,

I've tested the code and it runs perfectly.

Error # 3265 arises when you address a recordset field, that doesn't exist! So either

...
    .Fields(255) = varSplit(255)

or

    .Fields(18) = varSplit(273)

is wrong.

Check the number of table fields again.

If you really have 273 fields in your text file, your last index of varSplit is 272 and not 273 because the index starts with 0 (but that would lead to error # 9, so this is another story).

In regard to your first loop you don't have to update the recordset after setting one single field value (even if you have an ultra high performance computer ). Move the update command below "Next i", like this:

[COLOR=Blue]For[/COLOR] i = 0 [COLOR=Blue]To[/COLOR] 255
    .Fields(i) = varSplit(i)
[COLOR=Blue]Next[/COLOR] i

.Update

 

[curtinch]

So, the actual issue I was having was not with my code but it was with a data type in my table design. Below is now my working code that successfully brings in 274 tab delimited fields into two tables.

Public Sub ImportTextFile()
   ' to use the ADODB.Recordset, be sure you have a reference set to ADO
   Dim rst As ADODB.Recordset
   Dim rst2 As ADODB.Recordset
   Dim strFile As String
   Dim strInput As String
   Dim varSplit As Variant
   Dim intCount As Integer
      Set rst = New ADODB.Recordset
      Set rst2 = New ADODB.Recordset
   ' CHANGE THE TABLE NAME HERE
   rst.Open "Importtabledata", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   rst2.Open "Importtabledata2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   ' CHANGE THE TEXT FILE NAME AND LOCATION HERE
   strFile = "G:\Home\RiskMgtReports\AutoDatabase\fullextract.txt"
   Open strFile For Input As #1
   Do Until EOF(1)
       ' This counter is just to get to the applicable line before importing
       intCount = intCount + 1
       ' reads the text file line by line
       Line Input #1, strInput
       ' starts importing on the second line.  Change the number to match which line you
       ' want to start importing from
       If intCount >= 2 Then
           ' creates a single dimension array using the split function
           varSplit = Split(strInput, vbTab, , vbBinaryCompare)
           ' adds the record
           With rst
               .AddNew
                For i = 0 To 254
                    .Fields(i) = varSplit(i)
               Next i
               .Update
           End With
           With rst2
               .AddNew
' .Fields(0) is my primary key which in my case is the Application ID
                .Fields(0) = varSplit(0)
                .Fields(1) = varSplit(255)
                .Fields(2) = varSplit(256)
                .Fields(3) = varSplit(257)
                .Fields(4) = varSplit(258)
                .Fields(5) = varSplit(259)
                .Fields(6) = varSplit(260)
                .Fields(7) = varSplit(261)
                .Fields(8) = varSplit(262)
                .Fields(9) = varSplit(263)
                .Fields(10) = varSplit(264)
                .Fields(11) = varSplit(265)
                .Fields(12) = varSplit(266)
                .Fields(13) = varSplit(267)
                .Fields(14) = varSplit(268)
                .Fields(15) = varSplit(269)
                .Fields(16) = varSplit(270)
                .Fields(17) = varSplit(271)
                .Fields(18) = varSplit(272)
                .Fields(19) = varSplit(273)
                .Update
   End With
       End If
   Loop
   ' garbage collection
   Close #1
   rst.Close
   Set rst = Nothing
   rst2.Close
   Set rst2 = Nothing
   
MsgBox "Import complete"
End Sub

Thanks for the help Stargrabber, I was going crazy thinking it was my code.

 

[StarGrabber]

Glad to help.