Hi, thanks in advance...

If you were to delete some records from a table with an autonumber PK, will
those ID's that were used for the deleted records be available as new ID's
after a compact and repair?

I'm setting up a notifications system and am worried that with the high
volume on the table I may overflow the Long datatype in a few years. I plan
to have the notifications table purged on a weekly basis, and I have a
scheduled compact/repair on the backend on a monthly basis already... just
curious if I can plan on the PK re-using the purged values or if I should go
some other route for the IDs

Thanks,


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

On Fri, 8 Jan 2010 17:41:01 -0800, Jack Leach <dymondjack at hot mail dot com>
wrote:

>Hi, thanks in advance...
>
>If you were to delete some records from a table with an autonumber PK, will
>those ID's that were used for the deleted records be available as new ID's
>after a compact and repair?

Only if they were at the high end of the list (i.e. off the end, not out of
the middle)... and not with all versions of Access, either; some reset the key
only if you completely empty the table.

>
>I'm setting up a notifications system and am worried that with the high
>volume on the table I may overflow the Long datatype in a few years. I plan
>to have the notifications table purged on a weekly basis, and I have a
>scheduled compact/repair on the backend on a monthly basis already... just
>curious if I can plan on the PK re-using the purged values or if I should go
>some other route for the IDs

I was curious enough to do some calculations. If you add records at the rate
of one per second, starting now, not taking any weekends or holidays, you'll
run through the 4 billion odd autonumber values (it picks up at -2147483648
and counts up to 0 after it hits 2147483647) sometime in the afternoon of
2/15/2146. I.e. - NOT TO WORRY!
--

John W. Vinson [MVP]

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>>If you were to delete some records from a table with an autonumber PK, will
>>those ID's that were used for the deleted records be available as new ID's
>>after a compact and repair?
>
>Only if they were at the high end of the list (i.e. off the end, not out of
>the middle)... and not with all versions of Access, either; some reset the key
>only if you completely empty the table.

Jet 4.0/Access 2000 and newer only reset the autonumber seed if all
records were deleted. Jet 3.5/A97 and older did reset the seed even
if records were still present in the table.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/