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

I have been importing the same type of excel file in开发者_JS百科to 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.

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.

Go to design view of specific table in which you want to add duplicate data and select the row which add as duplicate and change the index to YES(DUPLICATE) than you add duplicate data in this table of MS-Access database......................

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

I had the same issue when importing Excel sheets into Access. The error for me was being caused by blank rows at the end of the Excel sheet. If this is the case for you, the simplest way to deal with it is to just copy the information from your Excel sheet into another sheet without the blank rows, for some reason the macros I tried to delete the blank rows didn't have an effect.

Also blank columns will have a similar effect but with the error stating something along of the lines unable to find column 11.