AutoNumber Field Reset, compacting does not help

[Guest]

I have an auto numeric field, every time I delete a record and compact the
database, the new record is not what is supose to be. for example, records
560,561, and 562.... I erase the record 562 and compact database. when I try
to add a new record it goes to 563. I am working on access 2000. thank for
help.

 

[Joseph Meehan]

I have an auto numeric field, every time I delete a record and compact
the database, the new record is not what is supose to be. for
example, records 560,561, and 562.... I erase the record 562 and
compact database. when I try to add a new record it goes to 563. I am
working on access 2000. thank for help.

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Guest]

Compacting the database no longer resets autonumber fields,
except when the table is empty. MS just wasn't able to make
it work properly after going from A97 (Jet 3.5) to A2K+ (Jet 4).

There are code samples that show how to 'reset the seed' using
VBA, but I can't give you an example at the moment.

(david)