ACC95: Import Spreadsheet Wizard Cannot Append Data to Table

ID: Q148165

The information in this article applies to:

SYMPTOMS

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

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.


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 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


  3. 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



STATUS

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


MORE INFORMATION

Steps to Reproduce Problem


  1. Open any database in either Microsoft Access 7.0 or Microsoft Access 97.


  2. On the File menu, point to Get External Data, and then click Import.


  3. 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.


  4. 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.


  5. 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.


  6. 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.



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. 


Keywords          : kberrmsg kbusage WzProb 
Version           : 7.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 22, 1999