ACC: TransferSpreadsheet May Not Allow Table Append

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you append data to an existing table from a spreadsheet using a TransferSpreadsheet macro action or a TransferSpreadsheet method in Visual Basic for Applications, you may receive the following error message:

   Field 'F1' doesn't exist in destination table '<table>'.
   Microsoft Access was unable to append it.

   The destination table must contain the same fields as
   the table you are pasting from.

CAUSE

The first row of the spreadsheet file does not contain field names; therefore, Microsoft Access assumes the fields are called "F1," "F2," and so on.

RESOLUTION

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 data in Microsoft Access; or you can change the spreadsheet to include field names 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: <your type>
         Table Name: <table in your database>
         File Name: <name of file, including path>
         Has Field Names: Yes
   

3. If you are using code, the HasFieldNames argument should be set to True, for example:

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         DoCmd.TransferSpreadsheet acImport, _
         <number of type of file to import>, "<table in your database>", _
         "<name of file including path>", True
   

MORE INFORMATION

Steps to Reproduce Behavior

NOTE: This example uses the Shippers table from the Northwind sample database (or NWIND.MDB in version 2.0).

1. Open Microsoft Excel version 7.0, and create a spreadsheet with the following data:

         9     Express      800-123-4567
        10     US Mail      800-569-4568
   
   

2. Save spreadsheet as c:\test1.xls.

3. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

4. Create a macro with the TransferSpreadsheet action, and the following values:

         Transfer Type: Import
         Spreadsheet Type: Microsoft Excel 5-7
         Table Name: Shippers
         File Name: c:\test1.xls
         Has Field Names: No
   

5. Run the macro. Note that you receive the error mentioned in the "Symptoms" section.

6. Create a new module, and enter the following function.

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Function ImportTest()
         DoCmd.TransferSpreadsheet acImport, 5, "Shippers", " c:\test1.xls", _
            False
         End Function
   
   

7. Open the Debug window (or the Immediate window in version 2.0), type the following line, and then press ENTER:

        ?ImportTest()
   

   Note that you receive the error mentioned in the "Symptoms" section.

REFERENCES

For more information about unexpected behavior with TransferSpreadsheet, see the following article in the Microsoft Knowledge Base: