Compact tool not resetting Autonumbers?

[Guest]

Hello,
I am running Access 2002 and have a split database. Several of my tables
use Autonumbers for their primary keys. In some cases, I have removed all
entries except for the very first one and when I run the Compact and Repair
tool, it does NOT reset the autonumber field to the next available number,
but simply continues from where it last left off before I deleted the
records. For example, I delete records 2-8, and after compacting, it inserts
9 in the Autonumber primary key instead of 2 or whatever. Does anybody know
of a special case that could cause this as everything I have read says that
it should restore unused numbers for use again.
Thanks a bunch!

 

[Guest]

Do the compact onthe backend database where the tables are kept.

That should do the trick.

SteveD

 

[Rick Brandt]

Do the compact onthe backend database where the tables are kept.

That should do the trick.

SteveD

In the newer versions (I think) the resetting of the AutoNumber on compact
only works when you delete all records so the AN is reset to (1). As long
as you leave some records in the table the AN is NOT reset to the lowest
un-used value.

 

[John Vinson]

Thanks for the info guys. I am thinking that Rick may be right as I have
compacted both the back end and the front end with no luck. I thought at one
very early point of development of the database that a compact did actually
clear up the previously used autonumbers... but not anymore apparently. I
was thinking one manual way the autonumber tables could be cleared up is by
exporting the table layout to a new database and then importing the data
afterwards. Maybe then it would start with 1 and count up cleanly again?
Just a thought...

And a good one. It may be the only way to do it with current versions
of Access.

Moral: NEVER EVER use an Autonumber if you want to assign any meaning
to the value. If you care what value the number has, use a Long
Integer field and maintain it yourself.

John W. Vinson[MVP]
(no longer chatting for now)