vba - Create Access table from text file -

i need create access (2007) table text file. know ahead of time columns should exist, time time vendors slip , submit text file contains incorrect number of columns. don't want specify columns in advance. want load data text whatever columns exist. qc.

the columns pipe delimited , there on 200 columns per record. there no column headers, there 1 line of header text file, , 1 line @ end states how many records there are. there may anywhere 1 on 5,000 records in text file. records identified crlf (windows).

here have far, , works (in reads file , places correct information in recordset (columns , records), , can count number of records), except select gives me error:

sub opentextado(strfilename string, strpath string)    dim cn adodb.connection   dim rs adodb.recordset   dim fld adodb.field   dim recs integer   dim strrecord string   dim strsql string    recs = 0    set cn = new adodb.connection    if right(strfilename, 3) = "txt"     'cn.open "driver={microsoft text driver (*.txt; *.csv)};" & "dbq=" & strpath & "\"  'need schema.ini file     cn.open "provider=microsoft.jet.oledb.4.0;data source=" & strpath & "\;extended properties='text;hdr=no;fmt=delimited(|)'"  'need schema.ini file   end if    set rs = new adodb.recordset   rs.open "select * testtext [" & strfilename & "]", cn, adopenstatic, adlockoptimistic, adcmdtext     'do until rs.eof   '  each fld in rs.fields   '    strrecord = strrecord & "|" & fld.value   '  next fld   '  strrecord = strrecord & vbcr   '  recs = recs + 1   '  rs.movenext   'loop    'debug.print strrecord    'recs = rs.recordcount    rs.close   set rs = nothing   msgbox "text opened , there " & recs & " records in table."    cn.close   set cn = nothing  end sub 

note: included both oledb version , text driver version - both seem operate identically. created schema.ini file looks this:

[test.txt] format=delimited(|) colnameheader=false 

both drivers seem need desregard column headers, despite "hdr=no" in oledb version.

the error is: "cannot update. database or object read-only".

i appreciate help.

could sequential read of text file, using count of pipe-delimited fields in first data line of file create table proper number of columns, write subsequent lines table? threw following together, seems work.

public function import_txt_to_db(strfile string) boolean on error goto errhandle  dim strline string dim intfilenum integer  dim blnfirstline boolean blnfirstline = true  dim vararray variant  intfilenum = freefile  open strfile input access read intfilenum  while not eof(intfilenum)      line input #intfilenum, strline     vararray = split(strline, "|")      if blnfirstline = true         'use count of fields in first line determine # of columns create         dim intcolcount integer         intcolcount = ubound(vararray)          dim strqry string         strqry = "create table tblimport ("         dim intctr integer         intctr = 1 intcolcount + 1             strqry = strqry & "[column_" & intctr & "] text(255),"         next intctr          strqry = left(strqry, len(strqry) - 1) & ")" 'get rid of terminal comma          currentdb.execute strqry          blnfirstline = false      end if      dim strqry2 string     strqry2 = "insert tblimport values('" & replace(strline, "|", "','") & "')"      currentdb.execute strqry2  loop  close #intfilenum import_txt_to_db = true  exit function  errhandle: import_txt_to_db = false  end function 

i did simple test folowing five-line text file

thomas|jefferson|virginia bill|clinton|arkansas jimmy|carter|georgia lyndon|johnson|texas george|washington|virginia 

after running code, here's (simple) table: