You cannot specify the table in an Access 2007 database into which you want to import an XML file

View products that this article applies to.

SYMPTOMS

Consider the following scenario. You want to import an XML file into a specific table in a Microsoft Office Access 2007 database. You follow the steps in the import wizard to the Import XML dialog box. In the Import XML dialog box, you notice that the Append Data to Existing Table(s) option appears. However, the dialog box does not let you specify the table into which you want to import the XML file. Instead, you experience one of the following results when you select this option and then click OK:

If the Access 2007 database does not contain a table that has the same name as the original table in the XML file, a new table is created. The new table has the same name, the same structure, and the same data as the original table in the XML file.
If the database contains a table that has the same name and the same structure as the original table in the XML file, the data from the original table in the XML file is appended to the existing table in the database.
If the database contains a table that has the same name as the original table but a different structure than the original table in the XML file, you may not import the data.

Back to the top

WORKAROUND

To work around this problem, follow these steps:

Note The following workaround is only an example. You may have to make some changes to fit into your scenario.

Import the table that is in the XML file into a new table in the database.

Note If a table exists in the database that has the same name as the original table that is in the XML file, you can rename the existing table in the database or modify the XML file to change the name of the original table to a different name before the import operation. When you have completed these steps, rename the table to the original name.
If you expect a new name for the imported table, rename the new table to the name that you want in Access 2007. However, if you want the data from the original table in the XML file to be appended to an existing table in the database, follow these steps:
1. In Access 2007, click the Create tab, and then click Query Design under Other.
2. In the Show Table dialog box, click the name of the new table, and then click Add.
3. Click Close to exit the Show Table dialog box.
4. Click the Design tab, and then click Append in the Query Type group.
5. In the Append dialog box, click the table name in the Table Name drop-down list. The table that you click in the drop-down list is the existing table in the database to which you want to append the data.
6. Select the Current Database option, and then click OK.
7. Specify the columns of data that you want to modify, and then click Run under Results.

Back to the top

Steps to reproduce the problem

Open an Access 2007 database.
On the External Data tab, click XML File under Import.
In the Get External Data - XML File dialog box, locate the XML file that you want to import, and then click OK.
In the Import XML dialog box, click Append Data to Existing Table(s).

You experience one of the results that are listed in the "Symptoms" section.

Back to the top