ACC95: Import Spreadsheet Wizard Cannot Append Data to Table

• Microsoft Access version 7.0

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 a Microsoft Access 7.0 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>'?

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

RESOLUTION

Use the TransferSpreadsheet action in a macro or the TransferSpreadsheet method in a custom function to import data from the spreadsheet into an existing table.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. This problem no longer occurs in Microsoft Access 97.

MORE INFORMATION

Steps to Reproduce Problem

1. Open any database in Microsoft Access 7.0.

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