Inserting accidentally deleted record

[Henley12]

Is there a way to put a record back into a table with the same autonumber it used to have? For instance, I accidentally deleted record # 8 and I need to reinsert it with the same number, which happens to be the Work Order ID field. Is that possible?

 

[Galaxiom]

Make a backup of your database.
Remove the primary key from the ID field of the table, change its format to number and rename it.
Add the missing record.
Add dummy records for any others deleted from the sequence and set one of their field values with a unique flag so these records can later be deleted.
Sort the table ascending by the old ID column.
Add a new ID field set as primary key autonumber.

Verify the new ID numbers by running a select query to return any rows where the new ID does not match the old. Hopefully their will all match.

Delete the old ID field and the dummy records.

 

[dcb]

Woooooaaaaa

A normal insert into query will do the trick - just specify the ID!

 

[Galaxiom]

So it will. All this time I have thought autonumbered fields were special because you could not directly enter or edit a value in the table.

Come to think if it now I have restored records with autonumbered fields from backups by simply appending them. Funny I didn't make that connection today. Looking for a hard answer to an easy question.