You are currently offline, waiting for your internet to reconnect

ACC2000: TransferSpreadsheet May Not Allow the Table Append Operation

This article was previously published under Q208380
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

When you append data from a spreadsheet to an existing table by using aTransferSpreadsheet macro action or a TransferSpreadsheet method in Visual Basic for Applications, you may receive one of the following error messages:
Field 'F1' doesn't exist in destination table 'tablename.'

Microsoft Access was unable to complete the append operation. The destination table must contain the same fields as the table you are pasting from.
Run-time error '2391':

Field 'F1' doesn't exist in destination table 'tablename.'
The first row of the spreadsheet file does not contain field names;therefore, Microsoft Access assumes the fields are named "F1," "F2," andso on.
You can use one of the following two methods to work around this behavior.You can import the spreadsheet into a new table, and then append that datain Microsoft Access; or you can change the spreadsheet to include fieldnames in the first row.

Method 1

  1. Import the spreadsheet into a new table named tblTemp.
  2. Create an append query to append the data in the tblTemp table to your existing table.
  3. Run the append query.
  4. Delete the tblTemp table.

Method 2

  1. In Microsoft Excel, insert a new first row in the spreadsheet. In this row, add field names that match the field names in the existing Microsoft Access table.
  2. In the macro, change the Has Field Names argument to Yes, for example:
    Transfer Type: Import
    Spreadsheet Type: My_type
    Table Name: MyDatabaseTable
    File Name: pathname\filename
    Has Field Names: Yes
  3. If you are using code, the HasFieldNames argument should be set to True, for example:
    DoCmd.TransferSpreadsheet acImport, _   <type of file to import>, "<MyDatabaseTable>", _   <name of file including path>", True					

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Excel and create a spreadsheet with the following data:
    A1: 9
    A2: Express
    A3: 800-123-4567
    B1: 10
    B2: US Mail
    B3: 800-569-4568
  2. Save the spreadsheet as C:\test1.xls.
  3. Start Microsoft Access, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
  4. Create a macro with the TransferSpreadsheet action and the following action arguments:
    Transfer Type: Import
    Spreadsheet Type: Microsoft Excel 8-9
    Table Name: Shippers
    File Name: C:\test1.xls
    Has Field Names: No
  5. Save the macro as TestImport, and then run the macro. Note that you receive the error mentioned in the "Symptoms" section.
For additional information about unexpected behavior with the TransferSpreadsheet action, click the article number below to view the article in the Microsoft Knowledge Base:
197525 ACC2000: Can't Import Numeric Field Names with TransferSpreadsheet

For additional information about unexpected behavior when importing data from Excel, click the article number below to view the article in the Microsoft Knowledge Base:
208582 ACC2000: 'Field 'F1' Doesn't Exist in Destination Table' Err Msg
prb field F1 doesn t exist in destination table run time error 2391

Article ID: 208380 - Last Review: 06/24/2004 18:10:00 - Revision: 2.0

  • Microsoft Access 2000 Standard Edition
  • kberrmsg kbinterop kbprb kb3rdparty KB208380