ACC95: Import Spreadsheet Wizard Cannot Append Data to Table

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q148165

Novice: Requires knowledge of the user interface on single-user computers.

SYMPTOMS

When you use the Import Spreadsheet Wizard, you cannot append spreadsheet data to an existing table in either a Microsoft Access 7.0 or a Microsoft Access 97 database. If you try to specify an existing table as the import destination, you may receive the following message:

   Overwrite existing table or query '<name>'?

You can click No to this error and specify a unique table name. Or, you can click Yes to overwrite the existing table.

NOTE: This behavior differs from earlier versions of Microsoft Access, which enabled you to append records from spreadsheet files to an existing table.

Back to the top

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.

Back to the top

Method 1

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

Back to the top

Method 2

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.

In the Microsoft Access 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

If you are using Visual Basic for Applications code in Microsoft Access, 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

Back to the top

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0 and Microsoft Access 97.

Back to the top

MORE INFORMATION

Steps to Reproduce Problem

Open any database in either Microsoft Access 7.0 or Microsoft Access 97.
On the File menu, point to Get External Data, and then click Import.
In the Import dialog box, select Microsoft Excel (*.xls) from the Files Of Type list. In the Look In box, locate a folder with an .xls file, and then click Import.
In the Import Spreadsheet Wizard, click the Next button three times to see the "That's all the information the wizard needs to import your data" box.
In the "That's all the information the wizard needs to import your data" box, type the name "Test1" into the Import To Table box, and then click Finish.
Repeat steps 2-5, but type the same name "Test1" into the Import To Table box. Click Finish. Note that you receive the following message:
```
      Overwrite existing table or query '<name>'?
						
```
You can click No to this error and specify a unique table name. Or, click Yes to overwrite the existing table.

Back to the top

REFERENCES

For more information about the TransferSpreadsheet action or method, search on the phrase "TransferSpreadsheet," and then view "Automate importing, exporting, or linking of data" using the Answer Wizard from the Microsoft Access 7.0 Help menu.

Back to the top

APPLIES TO

Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

Back to the top

kbbug kberrmsg kbfix kbusage KB148165

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top