Import text file using DoCmd.TransferText sometimes has 3011 error
Posted on 2009-12-17
Hi. I am trying to write VBA code to import data from a text file into an already existing table.
The VBA code is:
DoCmd.TransferText acImportDelim, "SPEC_091216", "RESULTS", Forms![Form1]![txt_RESULTS].Value
The import specification is saved as "SPEC_091216"
The already existing table is named "RESULTS"
The text file is in the same directory as the MSAccess dB and I tell Access the name of the file by opening Form1 and putting the text filename (e.g. Data.txt) into a text box.
When I execute this code, I get a 3011 run time error: "Jet database engine could not find the object 'Data.txt'. Make sure the object exists and that you spell its name and the path name correctly."
Ironically, this DOES work, if I do the following: File > Get External Data > Import > Browse for the file Data.txt > Select Advanced button > Select Specs button > Cancel out of everything.
I don't know why "looking" at the saved import specifications makes the import work. How can I change my VBA code to prevent the 3011 error. thanks.