Dealing with an autonumber linked field after data corruption

[MartyBoy]

Hi
I have provided a system that uses an autonumber field in a set of relationships to other tables. Every now and again the users manage to corrupt the primary table, which means the link is corrupted to the other tables after I remove the corrupted record.

This happens because of some internal error in Access that prevents deleting the corrputed record. The only way I have found of fixing the primary table is to copy all records up to the corrpted record into a new table, then copy all records after the corrupted record into the new table. This process requires reconstituting the autonumber field, which always provides new numbers for the records. I then have to run a series of update queries on each of the related tables to link them to the new ID numbers created in the primary table.

This process is tedious and expensive. Apart from using code to create another incrementing field, does someone have an idea how this be overcome?

 

[galileeian]

Im not sure if this suggestion is practicle: You are not able to delete the corrupted record, but if you can change a value in one of the fields to read 'corrupted' of someother kind of flag then you can create a query that selects everything from this table except flagged rows. You would then have to reference this query instead of the table. Whenever the problem occurs again you could flag the rows as corrupted.

If you have a large database set up then you probably wont want to change the whole database, its just an idea