importing dilemma

[Navy Nisi]

I have data from one access DB that has an autonumber Primary Key. It's been
exported to Excel so that it can be transferred to another Access DB that has
the same table and the same autonumber PK. When I try to import into the
second DB, I get an error message about the duplicate PKs. I tried to export
all the data without the primary key, thinking that it would auto assign a PK
when imported to the second DB, but received an error there as well. What is
the best way around this problem? Any assistance - as soon as possible
-would be appreciated.

 

[Ken Sheridan]

You should be able to do it by first importing the Excel worksheet as a new
table temporarily, and then basing an Append query on the new table,
inserting all columns apart from the autonumber column into the existing
table. This should assign new values in the autonumber column for the new
rows. You might find that you need to change the data types of some columns
in the temporary table before creating the Append query so that they match
those in the existing table as exporting to and importing from an Excel
worksheet does not always preserve the original data types.

If you find that some rows still can't be inserted into the existing table
it suggests that there might be an index violation other than that on the
primary key, i.e. one or more other columns is indexed uniquely in the
existing table and values exist in the rows being inserted which duplicate
existing values in the uniquely indexed column(s).

Ken Sheridan
Stafford, England