ACC95: Import Spreadsheet Wizard Cannot Append Data to Table (148165)
The information in this article applies to:
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
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.
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- 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.
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
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 7.0 and
Microsoft Access 97.
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.
Modification Type: | Major | Last Reviewed: | 10/31/2003 |
---|
Keywords: | kbbug kberrmsg kbfix kbusage KB148165 |
---|
|