Working code to import tab delimited txt file with more than 255 fields into two Access Tables -

this code below import tab delimited file on 255 fields 2 tables. make sure when design 2 tables fields have correct data types fields being imported. created tables using access import text file wizard. before using wizard deleted fields after 255 create first table , deleted first 255 create second table. hopes helps , below helped me project.

public sub importtextfile()    ' use adodb.recordset, sure have reference set ado    dim rst adodb.recordset    dim rst2 adodb.recordset    dim strfile string    dim strinput string    dim varsplit variant    dim intcount integer     set rst = new adodb.recordset    set rst2 = new adodb.recordset    ' change table name here    rst.open "appsimport1", currentproject.connection, adopendynamic, adlockoptimistic    rst2.open "appsimport2", currentproject.connection, adopendynamic, adlockoptimistic    ' change text file name , location here    strfile = "g:\home\riskmgtreports\autodatabase\creditappextract.txt"     open strfile input #1     dim integer    dim n long     n = dmax("index_number", "fullextract_hist")     until eof(1)        ' counter applicable line before importing        intcount = intcount + 1        ' reads text file line line        line input #1, strinput        ' starts importing on second line.  change number match line        ' want start importing        if intcount >= 2        n = n + 1            ' creates single dimension array using split function            varsplit = split(strinput, vbtab, , vbbinarycompare)            ' adds record            rst                .addnew                .fields(0) = n                 = 1 137                     if nz(varsplit(i - 1), "") = ""                     .fields(i) = null                     else                     if left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "jan m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "feb m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "mar m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "apr m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "may m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "jun m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "jul m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "aug m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "sep m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "oct m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "nov m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "dec m"                     .fields(i) = cdate(format(varsplit(i - 1), "mm/dd/yyyy"))                     else                     .fields(i) = varsplit(i - 1)                     end if                     end if                 next                .update                '.movenext 'i don't think should need            end            rst2                 .addnew                 .fields(0) = n                 .fields(1) = varsplit(0)                 = 138 274                     if nz(varsplit(i - 1), "") = ""                     .fields(i - 136) = null                     else                     if left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "jan m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "feb m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "mar m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "apr m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "may m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "jun m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "jul m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "aug m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "sep m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "oct m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "nov m" or left(varsplit(i - 1), 4) & right(trim(varsplit(i - 1)), 1) = "dec m"                     .fields(i - 136) = cdate(format(varsplit(i - 1), "mm/dd/yyyy"))                     else                     .fields(i - 136) = varsplit(i - 1)                     end if                     end if                 next                 .update             end        end if    loop    ' garbage collection    close #1    rst.close    set rst = nothing    rst2.close    set rst2 = nothing  end sub 

i admit you're trying here less ideal. don't work data many fields needed.

the solution here manage 2 different recordset objects.

public sub importtextfile()    ' use adodb.recordset, sure have reference set ado    dim rst adodb.recordset    dim rst2 adodb.recordset    dim strfile string    dim strinput string    dim varsplit variant    dim intcount integer     set rst = new adodb.recordset    set rst2 = new adodb.recordset    ' change table name here    rst.open "importtabledata", currentproject.connection, adopendynamic, adlockoptimistic    rst2.open "importtabledata2", currentproject.connection, adopendynamic, adlockoptimistic    ' change text file name , location here    strfile = "g:\home\riskmgtreports\autodatabase\fullextract.txt"     open strfile input #1     dim integer     until eof(1)        ' counter applicable line before importing        intcount = intcount + 1        ' reads text file line line        line input #1, strinput        ' starts importing on second line.  change number match line        ' want start importing        if intcount >= 256            ' creates single dimension array using split function            varsplit = split(strinput, vbtab, , vbbinarycompare)            ' adds record            rst                .addnew                 = 1 255                     .fields(i) = varsplit(i-1)                 next                .update                '.movenext 'i don't think should need            end            rst2                 .addnew                 = 256 ubound(varsplit)                     .fields(i) = varsplit(i-1)                 next                 .update             end        end if    loop    ' garbage collection    close #1    rst.close    set rst = nothing    rst2.close    set rst2 = nothing  end sub