Solved

Field 'F1' doesn't exist in destination table

Posted on 2006-07-13
10
752 Views
Last Modified: 2008-02-07
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
Comment
Question by:printmedia
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17100616
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17100642
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
 

Author Comment

by:printmedia
ID: 17100646
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 44

Expert Comment

by:GRayL
ID: 17100674
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
 

Author Comment

by:printmedia
ID: 17100696
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
 

Author Comment

by:printmedia
ID: 17100721
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17100751
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17105421
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
 

Author Comment

by:printmedia
ID: 17107632
Thanks rockiroads it worked great!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17108532
No probs

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Modal form 11 30
Access Update Query 1 20
IIF help, YN field 7 22
2 IIF's in Access query 25 32
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question