Duplicate Primary Key Values (1 Viewer)

Cedarguy

Access developer wannabe
Local time
Today, 04:11
Joined
May 8, 2012
Messages
39
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

Super Moderator
Staff member
Local time
Today, 07:11
Joined
Jan 23, 2006
Messages
15,364
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:11
Joined
Feb 19, 2002
Messages
42,981
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

Super Moderator
Staff member
Local time
Today, 22:11
Joined
Jan 20, 2009
Messages
12,849
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

Immoderate Moderator
Staff member
Local time
Today, 06:11
Joined
Feb 28, 2001
Messages
27,001
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

Access developer wannabe
Local time
Today, 04:11
Joined
May 8, 2012
Messages
39
Thank you all for your suggestions. I will review/try them all as appropriate and report my results. Appreciate your attention.
Regards,
 

Users who are viewing this thread

Top Bottom