Duplicate Primary Key Values

[Cedarguy]

Hi,

Don't know how this happened but during a brief period (3 months last year) these duplicate primary key values got created with legitimate field values. 140 records with 70 duplicate primary key values.

The table in question has a primary key and 2 foreign keys.

Two questions:
1. How could Access allow this? did I set up the table incorrectly?
2. How can I fix this problem? I need to assign a new primary key value to each duplicate while maintaining the relationship to the two other tables.

Thanks.

 

[jdraw]

I have not experienced this situation, but Allen Browne has an article that may shed some light on next steps.

Good luck.

 

[Pat Hartman]

Please post a database with just this table if possible. You might have to copy the whole database and delete all the other objects because just copying the table to a new database will most likely fix the corruption.

If there are child tables, you will probably have to fix the problem manually. If there are no child tables, then you can simply export the data without the autonumber, build a new table and import the exported data. You can then delete the bad table and CR.

 

[Galaxiom]

While it is possible for the autonumber seed to go wrong and prevent new records from being entered, I would be very surprised if a properly set up Primary Key would ever support duplicate values under any circumstances.

I would like to see that database.

 

[The_Doc_Man]

I've never seen that in a healthy database, either.

Is this a split database with Front-End/Back-End and linked tables, or a single-file database? If split, is it a homogeneous Access FE/BE or is the BE something other than Access?

I could imagine this in a corrupted database and I could also imagine this in a database for which the index happened to reside on a bad physical track.

Have you recently run a Compact & Repair on the file holding the table in question?

Can you run a ScanDisk cycle on the disk that holds the file that holds the table? (With the option to verify the tracks are readable?)

 

[Cedarguy]

Thank you all for your suggestions. I will review/try them all as appropriate and report my results. Appreciate your attention.
Regards,