Deleted Records - HELP!

[John Lane]

I am having a problem in which records seem to "disappear from a table, but
only occassionally. Then I noticed KB article # 287756, in which it states if
the last record of a table is deleted and the table has an autonumber, the
autonumber does not start at the next sequential number. Doee anyone know
what the autonumber does? Does it reuse an old number? if so, waht happens to
the original record that has that number? Thanks.

 

[Jeff Boyce]

John

If you are using an Autonumber datatype in a table, when you delete a
record, it's gone (OK, OK, so technically, if you dig into the Microsoft KB
articles, if you haven't closed the db or otherwise modified much of
anything, and you are very, very lucky, you MIGHT be able to recover the
deleted record -- good luck!).

If you are seeing gaps in the Autonumbers, that isn't because records have
been deleted (though that will happen if records were deleted). Autonumbers
are NOT guaranteed to be sequential.

If, for example, you start to add a new record, Access will reserve the next
Autonumber for it. If you decide to cancel and not add that record, Access
will NOT reuse the Autonumber. Ditto for if you delete a record. This is
by design.

After all, in a relational database, if you have a record in Table1 (with an
AutonumberID), and have related records in Table2, using that AutonumberID
as a foreign key, you don't really want to be able to give the Autonumber a
"new" record in Table1, do you?! That would make Table2's related records
look like they belonged to the new record, not the old one they actually
belong to...

Regards

Jeff Boyce
Microsoft Office/Access MVP