I received this error message when I tried to import a text file into a table in Access :

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

Through one of the postings, Miguel said something about do not set the format of importation. Doesn't that means do not define the data types for the fields in my table?

I appreciate any help. Thanks

access must be getting the idea that there should be a field called F1 from somewhere.

wild guess: you have an import specification that names one of the to-be-imported (not skipped) fields as F1 but you do not have a corresponding field in your table (fix: make a field F1 or skip that field in your import spec)

if you are not using an import spec, probably the following will help:
-are you using first row as field names?
-paste the first row onto the site anyway.
-text-search the text file for F1 and paste that row too

out of curiosity, what happens if you add a field F1 to your table?

izy

I had exactly the same problem. It seems to occur when trying to import Text files using macros or VBA. After much head scratching I solved by;

(1) Creating an Import Spec - File/Get External Data/Advanced
(2) Refering to the Import Spec using the TransferText vba command

This solves the problem where text files do not contain headers (for some reason this import method always assumes they do - even if you specify they don't). The Import Spec method defines proper headers and therefore avoids this "feature" of Access!

Hope this helps.

simon

Hello Simon,

Thanks for your reply.
I'm working with .adp front end. I tried to create Import Specification in Advanced ... the Save As is not available(grayed out). Did you also worked with .adp file? I thought if you can either link or import the files you should be able to create specification as well. Umm?

Alice

Hi Alice

I running Access97 using just a normal database (sorry I don't know what adp is).

The problem is outlined in a MS Knowledge Base Article 208582 for Access 2000 (see link below)

http://support.microsoft.com/default...b;EN-US;208582

Ultimately I guess you either need to copy the headers into the text file(s) - depending on how many there are, or somehow reenable the Import Spec function. Is the function part of an add-in?

Sorry my knowledge of Access is very limited.

Regards,
Simon

Hi,

the TransferSpreadsheet (or similar function) has a parameter specifying whether the input data has a Header Row. This means the firs row to contain the names of the columns, but this is not required.

- if this parameter is True, then the names of the columns the destination table (Jet or Sql-2000) must be the same as the names appearing in the header. I'm finding this rather cumbersome, so I'm not using this option.

- if the parameter is set to False, the the function expects columns named F1, F2, F3, etc in the destination table. This is true for both Jet(MDB) and Sql-2000(ADP) destinations. I'm finding this much more flexible.

rather unusual to have hard-wired names, but it works. So, I have defined a table in Sql-2000 with columns like:

CREATE TABLE t_txn_hist_buff (
auto_key int IDENTITY (1, 1) NOT NULL ,
F1 varchar (250) NULL ,
F2 varchar (250) NULL ,
F3 varchar (250) NULL ,
F4 varchar (250) NULL ,
F5 varchar (250) NULL ,
F6 varchar (250) NULL ,
F7 varchar (250) NULL ,
F8 varchar (250) NULL ,
F9 varchar (250) NULL ,
F10 varchar (250) NULL ,
F11 varchar (250) NULL ,
F12 varchar (250) NULL ,
F13 varchar (250) NULL ,
F14 varchar (250) NULL ,
F15 varchar (250) NULL ,
is_header bit NOT NULL DEFAULT (0),
invalid bit NOT NULL DEFAULT (0),
txn_date datetime NULL ,
CONSTRAINT txn_hist_buff_PK PRIMARY KEY CLUSTERED
(
auto_key
)
)


Then, the VBA code fragment is:

DoCmd.SetWarnings (False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"dbo.t_price_hist_buff", fn, False

Note that I had to use the "dbo" prefix in front of my table name. Apparently, Access Project does not use the standard name resolution of T-Sql. Anyway, it works, and you can see I could add a few work fields after F15.

HTH,

Andrew

Thank you, Simon!
Thank you, Andrew!
Unfortunately, I need to go to another approach at this time if not I think there is enough info for me to get the code working.

Thanks again.

Alice

Hello Guys!

I´m experiencing a similar issue at the time of trying to import a excel file to a table which already exists in the Access DB.

The error message displays "Field ´Period´ doesn´t exist in destination table"
However, the thing is that the field does exist in the destination table. As far as I´ve been checking it doesn´t seem to be due to format types, so I´m not sure what else could be causing this error.

Any guidance you can provide would be truly appreciated!
Thanks in advance!

Lale