XL: Cannot Append MS Excel Speadsheet to MS Access 7.0 Table

• Microsoft Excel for Windows, versions 2.x, 3.x, 4.0, 5.0
• Microsoft Excel for Windows 95, version 7.0, 7.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0, 5.0a
• Microsoft Access versions 7.0

SYMPTOM

The Microsoft Access version 7.0 Import Spreadsheet Wizard does not enable you to append Microsoft Excel spreadsheet data to an existing Microsoft Access table. You can only create a new table or overwrite an existing table.

This behavior is different than in Microsoft Access version 2.0, which offers you a choice between creating a new table or appending the Microsoft Excel spreadsheet data to an existing table.

WORKAROUND

To append your Microsoft Excel spreadsheet to an existing Microsoft Access version 7.0 table, first save it in Microsoft Excel as a comma-delimited text file (CSV). Then, import the text file into Microsoft Access version 7.0. You will then have a choice between creating a new table, or appending the data to an existing table. To do so, follow these steps:

1. Check that your columns have headers that exactly match the field names in your database.

2. In Microsoft Excel, click Save As on the File Menu.

3. In the Save As Type box, click CSV (Comma delimited)(*.CSV)

   This creates a text file with the same name as your Microsoft Excel workbook, but with a CSV extension. Only the data in the active worksheet is included in the text file.

4. On the File menu, click Close. When the confirmation dialog appears, click No.

5. In Microsoft Access, on the File menu point to Get External Data, and then click Import.

6. In the Files Of Type box, click Text Files (*.txt;*.csv;*.tab;*.asc).

7. Choose your text file, and click Import.

8. In the first Import Wizard dialog box, click Next.

9. In the second Import Wizard dialog box, click to select First Row Contains Field Names, and then click Next.

10. In the third Import Wizard dialog box, click to select In An Existing Table, click the table to which you want to append the data, and then click Next.

11. Click Finish, and then click OK. Note that your data has been appended to the table of your choice.

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

For more information about importing Microsoft Excel spreadsheets into Microsoft Access, click Answer Wizard on the Microsoft Access Help menu and type the following text on the Answer Wizard tab:

   import or link data from other programs and file formats

   ARTICLE-ID: Q141228
   TITLE     : How to Import Microsoft Excel File Into Microsoft Access