Import Text / .txt file to access table

[tuktuk]

my goal is to write code to import a statically named file to a tabel. i've tried usign the docmd.transfer text.

is this possible? I am getting an error with the txt header not matching my pre define field names.

here's my coding:

DoCmd.TransferText acImportDelim, , "RMT_Trns_UPLOAD", strFullTxtPath & "RMT_TXNS_CURRENT.txt", True

more on the error:
if i open the .txt the the first row has YYYYMM and then data below ...this needs to import to the first field of the table.....then there is a space in the txt file to the second header Ethnic....this needs to improt tot he second field of the table.

NOTE: the field names in my table match those int he .txt.

thnaks
tuk

 

[Joe4]

Be sure that not only do the field headers in your data file EXACTLY match the field names in your table, but that they also exactly match the field names in your Import Template ("RMT_Trns_UPLOAD").

Be sure that that the header row users the same delimiters as the actual data.

Be sure that there are no extra fields in your data.

 

[tuktuk]

here is the error.

Error 2391.
Field YYYYMM_Ethnic_XXXXXXX to the end of the headers Doesn't exist in the table "RMT_Trns_UPLOAD".

How do i verify this:
"Be sure that that the header row users the same delimiters as the actual data."

 

[tuktuk]

the deliminators for the header row and actual data match.

that is, i manually imported the data to the table "RMT_trns_UPLOAD" using the Tab delimination and selected where the txt contains a header. this completed

BUT STILL NO GO USING THE CODE...hmmmmm. any other suggestions?

 

[Joe4]

Error 2391.
Field YYYYMM_Ethnic_XXXXXXX to the end of the headers Doesn't exist in the table "RMT_Trns_UPLOAD".

This is telling you that the field names in the header and your import template do not match.

Maybe this field and all fields after do not have Tabs between them, so it recognizes it as one big long field instead of many different fields.

 

[tuktuk]

understood on the one big file...

so i imported the file.....using the header names. then made a copy of it so it is the exactly header names from the .txt file. this is the one i am usign in the code above, "RMT_trns_UPLOAD".

how to i make sure the code states that the delimination is where the Tab is? would that solve it?

T

 

[Joe4]

Go through the manual Import Wizard like you are going to load it again. Then just before you click on the final step to import the file, click on the Advanced button and save the import specs with a new template name. Then use this new template in your code.

 

[tuktuk]

mmmmm i've never used a template and am searching for the newly save one. where can i locate it?

 

[Joe4]

Where did the template "RMT_Trns_UPLOAD" come from in the first place?

All you have to do is set it like I described in the previous post, and make note of the name you are giving it. Then use that exact name you gave it in your VBA code.

If you do this through a macro instead of VBA (using the TransferText Action), it will list all the available templates that are set up for the database in a drop-down box under Specification Name).

You could always set-up the import using the Macro, then use the "Convert Macros to Visual Basic" functionality if you need it in VBA code.

 

[tuktuk]

sorry...never even heard of a template.

all i've done is used vba to attempt to imports.

how can i call the template in VBA?