Microsoft KB Archive/148165

= ACC95: Import Spreadsheet Wizard Cannot Append Data to Table =

Article ID: 148165

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



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

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
 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: Table Name: File Name:  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, _ , " ", _ "", True </ol>

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Problem
<ol> Open any database in either Microsoft Access 7.0 or Microsoft Access 97.</li> On the File menu, point to Get External Data, and then click Import.</li> 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.</li> 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.</li> 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.</li>  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:

<pre class="fixed_text">     Overwrite existing table or query ' '?

You can click No to this error and specify a unique table name. Or, click Yes to overwrite the existing table. </li></ol>

<div class="references_section">