MS Access 2007 Duplicate values (key violation error) and have eliminated duplicate values

Question

I have been importing the same type of excel file into MS Access 2007 and have had not problem until recently. The message says I have a key violation when I am overwriting a table. There were duplicates in the excel file (primary key field) which I deleted and I checked the excel file meticulously. I have reimported and its still showing the same message.

What course of action can I take to resolve?

Duplicate values (key violation error) Records that you are importing might contain duplicate values that cannot be stored in the primary key field of the destination table or in a field that has the Indexed property set to Yes (No Duplicates). Eliminate the duplicate values in the source file and try importing again.

Answer 1

All you can do is zero in on the problem. I am inclined to doubt it's a bug, but even if it is, you still have to find the precise trigger.

1. In the Excel file, pull the key column(s) together, and sort the Excel table for it/them. Add conditional formatting using a formula on that full range (if it started in A2 the conditional formatting would read =A2=A1, no dollar signs) with some bold, fill, etc. so that any dupes show up clearly.

2. Copy the Access table. Delete all keys, indices, validation rules, etc., one at a time, importing to the copy after each delete, until the import works.

3. Copy the rows in small batches (dunno how many rows you're talking, but say 50-100 at a time) to a new sheet, and link and import (to the original table) from the new worksheet until you can narrow down what row(s) the error is coming from. (If one small batch fails, keep making it smaller.)

If it indeed turns out not to be a violation but a bug, post the bad values and the table definition with all field sizes and key/index/validation composition back here.

Answer 2

Check your Access database table carefully. Access frequently decides to index fields on your behalf. These are generally not UNIQUE indexes (expressed in Access as a "Duplicates OK" property of No on the column) but perhaps you have some extra indexes you don't know about.

Also check to see whether you have an autonumber field in the table and make sure that the autonumbering hasn't gotten messed up (remove and re-add the autonum declaration in the empty table).