Importing data

[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.

 

[Rob Parker]

Your approach, of exporting without the PK and then importing, should be OK.
What was the error that you got when you did that?

An alternative, and almost certainly simpler method, is to not use Excel as
an intermediate step. In the second database (the one where you want to
import the records to), set up a link to the table in the first database.
Then set up an append query to copy the records in this linked table into
the table in the second database; don't include the PK field in the append
query.

HTH,

Rob

 

[Navy Nisi]

Thank you for the feedback. Yes, I agree, linking and appending would be
easier and eliminate the problem I'm having. However, the reason for the
intermediate excel step is due to the fact that the first db is at a remote
location. The data has to be added to the parent DB (#2) by bringing it in
on a thumb drive.

The original error message I received was in regards to the duplicate
primary keys causing referential integrity. Then, when I exported the data
to excel without the primary key, it just gave me a general error message
that said the data could not be imported.

Another suggestion I received was to import as a new table and then
append...which may have to work for now, but was trying to minimize the
number of steps needed to import the data.

Thanks again for the feedback. If you have any other suggestions, I am all
ears.

Navy Nisi

 

[Dale Fye]

Are you only importing a single table? If so, what has already been
mentioned should work.

However, if you have multiple tables, and one or more of them contain
foreign keys back to the PK of the "main" table, then your task will be a
little more complicated. If that is the case, post back and I can provide
some guidance.

Dale