Field 'F1' does not exist in destination table (1 Viewer)

[babakuki]

I created a Macro to automate importing a delimited text file to MS access. I have created the destination table containing the same number of fields as my text file.

However, on running the macro, MS Access came up with error message as below:

Field 'F1' does not exist in destination table. Microsoft Office was unable to complete the append operation. The destination table must contain the same field as the table you are pasting from.

Can anyone help please?

 

[SteveSchapel]

Babakuki,

Can you try to import this file into a new table, which will help to identify where the problem is coming from?

I assume you are using a TransferText action in your macro. Do you have an Import Specification defined?

 

[gemma-the-husky]

this is often a problem with importing excel files

excel thinks the spreadsheet has a column F. You may have used this for a temporary data, maybe -

the import is no doubt importing to an existing table, with only 5 columns set up.

So:

1. physically delete column F from the spreadsheet and try again
2. consider using a csv if possible instead of a spreadsheet - -as importing a csv is far more reliable anyway.


-------------
steve, i dont think you can have an import spec, with a xls file - thats what causes the problem - you lose control over the exact nature of the import

 

[SteveSchapel]

Dave,

What makes you think it is a .xls file?

As for the column F idea... no that's not it. Access names any columns in the source file that are not included in the destination table as F1, F2, F3, etc.

 

[namliam]

Dave is right, this is a VERY common problem with excel files a VERY VERY common problem.

However Steve you are right as well, any unknown column is called F1, F2 etc, it is not limited to the column F.

If the OP is importing a delimited file, there probably is an extra delimiter in the file, very common with comma delimited files.

If the OP were using Excel, there wouldnt even have to be actual a 'used' column. The column can be empty but used before and excel/access still regards it as part of the table for some reason.

In both cases the most easy way to track it is to import into a new table and find the error/extra column then fix it.

 

[babakuki]

Thanks everyone for your input

@ Steve Schapel, i have manually imported the same file into the same table without any problem. I only get this error when i try same process using a macro...

I didn't define an Import Specifcation because the transfer type im using is import delimited. Also the file is a csv file.

Will appreciate more suggestions resolve this issue.

Thanks

Babakuki

 

[gemma-the-husky]

Dave,

What makes you think it is a .xls file?

As for the column F idea... no that's not it. Access names any columns in the source file that are not included in the destination table as F1, F2, F3, etc.

Steve

You are correct - I misread and assumed it was a xls being imported, and jumped to conclusions.

 

[ghudson]

I would open the file in notepad and see if you spot anything out of place or unexpected.

 

[SteveSchapel]

Babakuki,

Import of a csv file should be done with a TransferText action in your macro, and using an Import Specification would be a great idea.