Restoring deleted Autonumber (1 Viewer)

[shawnvw]

I was using an autonumbered ID field to link the table to others. Some of my records have been deleted for one reason or another; so the ID numbers go from 7 directly to 13.


Is there any way of adding a new record that reuses the ID of one of the deleted records? It would sure be easier than than changing the corresponding ID in the linked tables and changing all the 8s to 13s.

 

[Starman]

If the records are deleted in one table, why are they still linked in another table?

There's no way of "recreating" deleted autonumbers. If you absolutely had to (and I don't know why you would), you would have to create a new table and copy your records into it. It sounds to me like you have some design issues with your db that you need to work out. The whole point of having autonumber PKs is that they are meaningless and it won't matter what they are.

 

[aqif]

I agree with Starman that linked table should never have free floating IDs. You probably need to sort out unlinked record problem first and then make sure that you put "Enforce Referential Integrity" in the relationship between your joined tables.

 

[shawnvw]

If the records are deleted in one table, why are they still linked in another table?

They're not linked, but I want them to be. While I was entering the data from each hand-written log sheet into the table, I wrote the ID number -- assigned by Autonumber -- onto the sheet. When the record got deleted somehow -- probably when I was trying to make corrections -- the number on the sheet was no longer correct. I know it would be easier to enter the log as a new record and just write the new number on the page, but they were in a certain order. Besides, I may find another reason for adding a lost Record 8 into the table.

The whole point of having autonumber PKs is that they are meaningless and it won't matter what they are.

I thought the point was that you'll always have every number once, no more or less.

So I'm supposed to use some other way of identifying each physical log sheet within the table? I guess I could add a new column for that. Grrr...

 

[neileg]

You can't enter into an autonumber field directly, but you can append to it. So if you create a replica of the table (but with an ordinary number instead of an autonumber) and enter your missing records, you can append those records to your main table and populate the autonumber field.

And yes, autonumbers offer no guarantee of being sequential with no gaps, just unique. It's good practice to use autonumbers as PKs (I always do) but not to use them for a field that is presented to the user as having a meaning.

 

[Pat Hartman]

Once you have recreated record 8, fix the RI setting to prevent this from happening again. Cascade delete will cause the related child records to be deleted. If you don't check Cascade delete, Jet will prevent you from deleting a one-side record if there are any many-side records dependent on it so in your case, record 8 would not have been deleted because it had dependents.