Question Delete Duplicate Records from Access Database

[SterMisia]

Hello everyone.

I am using visual .net in one of my programs and i store data in Access Database.
An unexpected power failure make the database unstable.
I tried compact and repair but 1 table lost primary key.
I checked the table which had the problem and i tried to insert primary key (Field name ID AND AUTO NUMBER).
I had an error message duplicate records exist.
I checked the table and i show that 2 records had the same ID number.
I can mark the row to delete and then reset primary key manually .
Is there a way to delete the one of the duplicate rows and leave the other from the visual .net ?

SterMisia

 

[IanRyder]

Hi,

From experience I would suggest that you NOT try to “fix” this table. You are asking for trouble later. However, recreating the table depends on how crucial this Primary Key is as a Parent Identifier to other Child records in other tables in your database.

If the Primary Key is NOT crucial to relationships then just recreate the table with the same schema as the old table and append the records from the old table to the new table, ignoring the AutoNumber column so it just regenerates itself, and then delete the old table. Problem solved.

If the Primary Key IS crucial to relationships then recreate the table with the same schema as the old table but make sure that you leave the ID column as a NUMBER data type, with NO indexes, and NOT AutoNumber. Then append the records from the old table to the new table making sure that you DO append the OLD ID column to the NEW ID column. Once done, go to one of the duplicate ID’s in the new table and set it to the maximum ID you have so far plus 1 (i.e the NEXT number in the AutoNumber sequence). Finally, re-design your new table and reset the ID column to AutoNumber and set back to a Primary Key and save. Once all that's done then you can get rid of the old table.

Hope that helps.

Cheers,

Ian

[Edit]
Actually, just thinking about my old access days and if I remember right, you need to make sure that the Duplicate ID that you reset is the LAST record in the Data Table so that the AutoNumber re-sequences from that last number. You may need to cut and paste this record from its current location in the table to achieve this.

 

[SterMisia]

Ian thank you for your quick answer.
When i create the new table with same schema as the old one , i append the records from the old table.
I can't re-design the new table and set the ID to column to auto number cause data already exists.
Am i doing something wrong?
I am in the second option cause primary Key is crucial to relationships to other tables.

 

[IanRyder]

Hi,

It just shows much I remember off the top off my head without testing. LOL

OK, I got the resolution way wrong. What you need to do is:-

Re-design the OLD table and change the AutoNumber field to a Number field and get rid of the PrimaryKey Index. Save. Then find the duplicate record in the old table, cut and paste it to the end of the table and change the ID Column of that record to what would have been the Next Auto Generated Number. You have now forced the ID column to become unique again in its values.

Now, recreate the new table with the same schema as the OLD table but make sure that the ID Column IS an AutoNumber Column set as a PrimaryKey. Now create an append query and add all the old records to the new table making sure that the ID column IS included in the Append query to copy the existing values to the AutoNumber column. This forces a regeneration of the PrimaryKey in a AutoNumber column to the CURRENT values that you have now and the fact that you make sure the duplicate record is at the end of the data tables makes sure that the AutoNumber then starts to sequence again from that last number.

Sorry about that and I should have retested my memory first before posting.

Cheers,

Ian

 

[SterMisia]

Ian

Thank you very much.
It worked fine.