Microsoft Access table dropped primary key and index

20 pts.
Tags:
Access Database
Microsoft Access
Microsoft Access 2007
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    Access has the Find Duplicates Query wizard that can help you build a query to find the duplicates
    1,740 pointsBadges:
    report
  • Meandyou
    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
    5,220 pointsBadges:
    report
  • Swd144
    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.
    20 pointsBadges:
    report
  • msi77
    A few ways to delete duplicates: How to delete duplicates?
    1,670 pointsBadges:
    report
  • Meandyou
    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.
    5,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following