Solved: problems automating import from .txt

[eed]

Hi, all,

I have a tab-delimited text file of records that were exported from Reference Manager. I want to create an automated process to import these records into an existing Access table.

When I use the the Import Text wizard via the File>Import menu command, everything works fine, there are no errors, and I get exactly what I want, without providing any additional details beyond the filename and table name (well, and the fact that the text file is tab-delimited).

However, when I use the TransferText method (in a Macro or in VBA, either one), the process fails and I get an error message stating that "Field 'F1' doesn't exist in the destination table."

There are no field headings or anything included in the text file: just field values and tabs. I can't figure out where Access is coming up with this alleged "Field 'F1.'" Neither the text file nor the database even contain the value 'F1,' in any context! Below is the VBA I have tried to use (strFile is a variable containing the filepath to be imported; it is set by the user's selection in a FileOpen dialog box):
DoCmd.TransferText acImportDelim, , "IMPORT_TEMPLATE", strFile, False
Does anyone have any insights into why I might be receiving this error and how I can get the same beautiful results of the menu command through an automated procedure? Much appreciation for any help!!!

~ eed

[stanl]

Your best bet is to make a schema.ini entry in the directory where the transfer takes place. After that I would use ADO, but DAO seems to be your preference.

http://support.microsoft.com/kb/210073

can get you started. Stan

[eed]

stanl,

Thanks for the tip. I admit that I don't entirely understand the MS KB article on first glance, but I will give it some dedicated examination and see whether this solves my import problem.

Thanks! =)

~ eed

[eed]

stanl,

As a modification of your suggestion of a schema.ini file, I used the Access Import/Export wizard to define an (Access-stored) import specification for my file. I plugged that import specification in as part of my TransferText command, and that solved my import problem.

I realized the problem was that Access was giving the fields in the text file default names (like F1, F2) and didn't know how to match them up to the import table. In the import specification I could name the matching positions so it would connect them. Still makes me wonder, though, why the wizard could get over this hump without my help but the code needed to store all these specification details.

Anyway, THANKS so much, this thread is solved!

~ eed

[stanl]

I'm glad you solved it. As an FYI, if you omit an import spec in a TransferDatabase or use ADO "SELECT INTO..", Access automatically looks for a schema.ini entry. Since schema entries can be built or modified at runtime it is good knowledge to keep in reserve. Stan