reset autonumber to a specific value

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

My autonumber has incremented without adding new records.
How do I reset it back to where it is supposed to be. I
tried following the help instructions, and it did start
the next record with the correct number, but it
immediately jumped to the old internal number after I
added one record???? I have defined this field as a
primary key with autonumber.
Thanks,
Tom
 
Tom, the easiest way to reset the AutoNumber is to Compact
and Repair. This only works, however, if the table is
empty, in which case the autonumber is set back to 1. If
you want to reset a table that already has information in
it, you must place the data in a temporary table (or just
copy into memory), delete the main, compact and repair the
database, and then re-import the data into the original
table. You must do this for every "hole" in the
autoincrement sequence.
 
That's how autonumbers work: there's no guarantee that they're going to be
gap-free.

Autonumbers serve one purpose: to provide an (almost guaranteed) unique
value that can be used as a primary key. They fulfill that purpose with or
without gaps. Seldom, if ever, should the value of the autonumber field be
shown to the end user.

If the value of your autonumber fields is important to you, perhaps you
should be using a different data type and setting the values yourself.
 
My autonumber has incremented without adding new records.
How do I reset it back to where it is supposed to be. I
tried following the help instructions, and it did start
the next record with the correct number, but it
immediately jumped to the old internal number after I
added one record???? I have defined this field as a
primary key with autonumber.
Thanks,
Tom
Click to expand...

As Trent says, this isn't particularly easy to do!

One note: An Autonumber has one purpose, and one purpose ONLY: to
provide an almost-guaranteed unique key. It is NOT intended to be
visible to users, as it will always have gaps. Deleting a record will
introduce a gap; hitting <Esc> while entering a new record will
introduce a gap; running an Append query will introduce a gap (often a
large one); the autonumber can even become random, for instance if you
replicate.

Solution: don't use an Autonumber if you want the value to be gapless
and sequential. Instead, program your own Custom Counter - there are
many threads in this newsgroup describing how to do so, check it out
with Google Groups. Or, use an Autonumber Primary Key and just don't
display it to user view so they won't worry about it.
 
Thanks Trent. Your suggestion worked!!
-----Original Message-----
Tom, the easiest way to reset the AutoNumber is to Compact
and Repair. This only works, however, if the table is
empty, in which case the autonumber is set back to 1. If
you want to reset a table that already has information in
it, you must place the data in a temporary table (or just
copy into memory), delete the main, compact and repair the
database, and then re-import the data into the original
table. You must do this for every "hole" in the
autoincrement sequence.

.
Click to expand...
 
Back
Top