Solved

Field 'F1' doesn't exist in destination table

Posted on 2006-07-13
10
713 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
 
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
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.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now