Microsoft Access table dropped primary key and index
I am using MS Access 2007 on Windows XP Professional. The main table in my Purchase Ordersdatabase has dropped its primary key and index (no duplicates). Previously, it worked without any problems, and caught duplicate values. Now, when I try to reset the primary key, I get the message, "The changes you requested to the table were not successful because they would create duplicate values, in the index, primary key, or relationship..." Is there any possible solution?
Software/Hardware used:
Software/Hardware used:
ASKED:
August 10, 2009 4:43 AM
UPDATED:
August 27, 2009 4:31 PM
Answer Wiki:
You need to look through the table, and find the records which have duplicate values in the Primary Key column, change that value (and the corresponding values in any other tables which relate to that table) then put the primary key back in place.
To see all answers submitted to the Answer Wiki: View Answer History.
Access has the Find Duplicates Query wizard that can help you build a query to find the duplicates
here is some SQL to find “duplicate” values (I put quotes around duplicate, because without a unique constraint, the database really does not consider them duplicates)
SELECT COL1, COUNT(*) AS OCCURS
FROM TABLEA
GROUP BY COL1 ;
You might add a WHERE OCCURS > 1 to list only the “duplicates”
Steve
Thx for the advice. I did look through and find duplicate values via Excel’s find duplicates, which, I’m sure, was slower than using the Access or SQL find dupes query. In the future, if this happens again, I will keep this in mind.
However, I’m not sure how the dupes got in there in the first place, since I did have the primary key and index set so that it was catching dupes. I learned a big lesson in making backups of my database, even if it isn’t finished.
A few ways to delete duplicates:
How to delete duplicates?
You say you are not sure how the dups got there. Well, they got in there while the PK was missing. When the PK was present it prevented the duplicates. then the PK went AWOL and duplicates were no longer rejected.