VBA Docmd.TransferText

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
How to import data

Hello Paul

I am sorry about my bad English :D but i shall try to explain.

You tried to import an TXT file into a table. there is two steps in the process to do so.

1. use the Access "data import guide" to build an import specification. you can save this definition using the guide tool. look fore it and you will find it below to the right in the import guide box.

2. Write this VBA code (command button):

Private Sub readfilebutton_Click()
Dim filename As String
filename = InputBox("import file:", "import ", "c:data.txt")
DoCmd.TransferText acImportFixed, "table importspec", "accessdata", filenavn
end sub

"Table importspec" = importspecifikations
"Accessdata" = table name

the most important part is the "table importspec" part, this part is the definition you made during the use of the import guide. this part will tell the data where to be placed in the table.

I hope this can help you.

Venlig hilsen
Jens Godik Højen
Faroe Island
 
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.
 
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.
 
Back
Top