Microsoft Access table dropped primary key and index
Home > IT Answers > Database > Microsoft Access table dropped primary key an...
Swd144
20 pts.
0
Q:
Microsoft Access table dropped primary key and index
Microsoft Access, Access 2007, Access Database
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?
ASKED: Aug 10 2009  4:43 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46810 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
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.
Last Answered: Aug 10 2009  6:14 AM GMT by Mrdenny   46810 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Randym   1410 pts.  |   Aug 10 2009  1:26PM GMT

Access has the Find Duplicates Query wizard that can help you build a query to find the duplicates

 

Meandyou   1840 pts.  |   Aug 10 2009  2:07PM GMT

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

 

Swd144   20 pts.  |   Aug 10 2009  4:30PM GMT

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.

 

Msi77   800 pts.  |   Aug 10 2009  5:52PM GMT

A few ways to delete duplicates:
How to delete duplicates?

 

Meandyou   1840 pts.  |   Aug 27 2009  4:32PM GMT

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.

 
0