IN 'mytextfile.txt' syntax - access

Question

i would like to use this syntax to update a table in access based on data from a txtfile.

fenton in his comments on this answer: http://stackoverflow.com/questions/2992168/read-text-file-line-by-line-and-insert-update-values-in-table/2992337#2992337

said that this is possible and i would like to see the exact syntax please

Answer 1

There are a number of examples in Stackoverflow, eg http://stackoverflow.com/questions/327658/slow-msaccess-disk-writing/327773#327773

strSQL="INSERT INTO tableX ( Name1,Name2 ) " _
& "SELECT Name1,Name2 " _
& "FROM [ltd.txt] IN '' [Text;Database=c:\docs\;HDR=YES;]"

You can also refer to a file by using the connection string that would be used in a linked table:

strSQL = "SELECT SomeField " _
& "FROM [Text;HDR=YES;FMT=Delimited;IMEX=2;DATABASE=C:\SomeDir\].MyTextFile.csv" 

Answer 2

Open "TESTFILE.TXT" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
  Line Input #1, TextLine ' Read line into variable.
  'Parse string into individual fields
  'Execute insert/update SQL statement
Loop
Close #1 ' Close file.