• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1494
  • Last Modified:

Field 'F1' doesn't exist in destination table

Hi all.

I keep getting this error when I try to run a macro that imports data into a table called Prices from a csv file called Prices.csv


This macro has been working for the past 2 years and today it failed so I tried to "Run" it myself and it gives this error:

Field 'F1' doesn't exist in destination table 'Prices'

My csv file does not have any headers and the macro has the "Has Field Names" set to no.

Thank you in advance.
0
printmedia
Asked:
printmedia
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you import data from a csv without that csv file having field names on top, Access will assume that the field names are F1, F2, F3, F4, etc.

If you import your data into a new table, this is no big deal, as Access will create them for you.

If you import your data into an existing table, and it does not have fields named F1, F2, F3, then the error is correct.
0
 
rockiroadsCommented:
Try building a specification and specify fields in your table

To build an import specification:

1. Go to File/Get External Data/Import, select filetype .txt
2. Click the, "Advanced...," button to open the specification;
3. Edit the specification as desired; and
4. Save.

Pass the specification name to TransferText to have Access use your saved definition

DoCmd.TransferText acExportDelim, "SpeciName", "tableName", "Filename", "Hasfieldnames"
0
 
printmediaAuthor Commented:
The csv file has no field names, but the table does have field names, but I noticed that my csv file only has 5 columns while the table in access has an extra column called upsize_ts, which is empty. Could this be the problem?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GRayLCommented:
If you import a csv file into a table without headers and "Has Field Names" is set to No, don't all the fields get named F1, F2, F3,...Fn?  Does a table get created and does some other part of the macro try to operate on field F1?  Has the format of the csv file changed?  Obviously, something has.
0
 
printmediaAuthor Commented:
The macro first deletes the data in the Prices table then it imports the csv file. The table already has the 5 field names. So it tried to run but stopped, so the table is now empty because it did the first part of deleting the old data.

The file has not changed.
0
 
printmediaAuthor Commented:
The first Action of the macro is RUNSQL which deletes the data in the Prices table.

The second Action is TransferText and the Transfer Type is Import Delimited, I specify the Table Name as Prices and the File Name is the path where the csv file resides and finally "Has Field Names" is set to No.
0
 
rockiroadsCommented:
If u delete the table Prices, that may work, as import will recreate it

I searched MS site for possilbe solutions to this and it came up with this, it also suggests import specifications
http://support.microsoft.com/default.aspx?scid=kb;en-us;142186

0
 
jefftwilleyCommented:
follow Rocki's first entry.
Rename the old table

Go to file/get external data/Import

go find your .csv file

when the wizard opens, set field names to no

click on the advanced button

when that opens you can name your fields whatever you want

finish that up and SAVE it. Remember the name, that's your new Import Spec

save to a new table (your old table name)

and it creates the new table and imports the data.

Voila!! No More F1, F2, etc...


0
 
printmediaAuthor Commented:
Thanks rockiroads it worked great!
0
 
rockiroadsCommented:
No probs

Its painstaking to create it first time, but easy thereafter
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now