VBA Docmd.TransferText

[pgilbert11]

Am currently trying to setup an import function on a database which will
import data from a txt file to a database table.

The issue that i am having is that it keeps returning the error:
Run-Time error '2391'
Field 'F1' doesn't exist in detination table 'tbl1'.

I cannot workout what it is going on about.
Could anybody please shed some light on this issue.

Thanks
Paul

 

[Jerry Whittle]

1. Please post your code.

2. Manually import the file. When you get to the first dialog box in the
Import Text Wizard, click on the Advanced button on the lower left. Here you
can create an import specification including the text delimiter. Save the
import spec and remember the name.

TransferText allows you select an import specfication.

 

[pgilbert11]

Current code as follows
DoCmd.TransferText , , "Transport", "c:\Database\Transport.txt"

 

[Jerry Whittle]

Hi,

You've left out the TransferType argument so the text file must be
delimited. If it's a fixed-width file, it probably won't import correctly.

You also aren't using an Import Specification. If there's anything strange
in the import file, such as a weird delimiter, an import spec might fix the
problem.

Last make double sure that there is a table named Transport. If not it could
fail.

 

[John Nurick]

Hi Paul,

When you're importing to an existing table, the field names in the
source must match the field names in the destination.

If you're using this statement:

DoCmd.TransferText , , "Transport", "c:\Database\Transport.txt"

you are telling Access that the file you're importing doesn't have a
header row containing the field names. In that situation, the import
routine assigns default field names F1, F2... - and then raises an error
when these don't match the names in the table you're importing to.

If the file does have a header row with the field names, use the
HasFieldNames argument of TransferText:

DoCmd.TransferText , , "Transport", "c:\Database\Transport.txt", True

If not, you can link the table using

DoCmd.TransferText acLink, , "Transport", _
"c:\Database\Transport.txt", False

and then use an append query to move the data to its final destination,
chaning the field names as it goes.