Import Excel Into Access - Primary Key Error

[Karl Burrows]

I am trying to import an Excel table into an existing Access 2003 database.
I first exported all the field names from Access to Excel and then pasted
the values from another Excel worksheet to match the column names I want to
import. I then ran through the import wizard and got finished and received
a primary key error. One primary key is the autonumber field. The export
to Excel included the ID field which I have tried with blank values, other
numbers, etc and get the error anyway. How can I import this data in Access
and work around the primary key issue? I even tried a text import and got
the same error.

Thanks!

 

[Ken Snell]

You want the primary key value from the EXCEL spreadsheet for the final
table?

Import the EXCEL file into a temporary table. Then use an append query to
copy the data, including the primary key field, to the permanent table --
the append query will put the primary key value into the target table's
autonumber field, if that is what you map it to, and override the
autonumber's normal incemeting behavior.

 

[Karl Burrows]

No, I already have a primary key in the table. I don't care what auto
number it brings into the database. I just added the column so it would
match the fields in Access.

I've never tried an append query, but we'll give it a try!

 

[Karl Burrows]

I removed the ID autonumber from the append query and it still won't import.
I get key violations errors. I do have secondary keys that are not indexed
in the table as well.

 

[Jamie Collins]

...

I removed the ID autonumber from the append query and it still won't import.
I get key violations errors. I do have secondary keys that are not indexed
in the table as well.

Here's some practical advise. How about executing e.g.

SELECT *
INTO MyNewTable
FROM [Excel 8.0;Database=C:\MyWorkbook].[MySheet$]
;

and see what you get. You can add keys and indexes after the import.

Jamie.

--

 

[Karl Burrows]

I figured it out. I had a secondary key that had a relationship with
another table that required a value the worksheet didn't have. I added a
column and put in a current field value and it worked fine.

...

I removed the ID autonumber from the append query and it still won't import.
I get key violations errors. I do have secondary keys that are not indexed
in the table as well.

Here's some practical advise. How about executing e.g.

SELECT *
INTO MyNewTable
FROM [Excel 8.0;Database=C:\MyWorkbook].[MySheet$]
;

and see what you get. You can add keys and indexes after the import.

Jamie.

--