Transfer Text using Access 97 (Access 97)

Topic

New Reply

I have a GUI created using Excel 97 VBA. What is created by the end user is a delaminated text file. I can import the text file to my database using

File>Get External Data>Import> and selecting the appropriate text file.

There is no issue. When it try to set up VBA code to import this text file I get runtime error 2391 saying that field F1 is not available in my database. Here is the code I am using.

Docmd.TransferText acimportdelim, “”, “Recurrent Table”, recurpath & fName,0

I have told it to import delaminated, there is no specification name, the table name is correct, the path name and file name are string variables that reach the correct file, and there is no header row. I need to import this using VBA because the file names will change depending on the user and will never be the same and there will be multiple files being imported. Am I missing something? Dose anyone have any advice. I am new to using VBA for Access, but I am experienced using VBA in Excel. Please help.

Thank you.

Reply | Quote

Replies

Your import command
Docmd.TransferText acimportdelim, “”, “Recurrent Table”, recurpath & fName,0
should probably look like:
Docmd.TransferText acimportdelim, , “Recurrent Table”, recurpath & fName,False

HTH
Pat

Reply | Quote

According to my experiments you will definitely need to use an import specification.

To create this you go through the manual import up to the point where you are about to click ‘Finish’. Then you click ‘advanced’. You then need to alter all the field names to the actual names of the destination table. Then you click ‘Save specification’ and give it a name.

Someone else might know a better way than this but this one works.

Reply | Quote

Since you say you’re experienced in XL VBA…

Why not use ADO to update the data to Access directly?

Reply | Quote