Solved

Field 'F1' doesn't exist in destination table

Posted on 2006-07-13
10
868 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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