reset an autonumber

[Rpettis31]

I have an autonumber that is generated for each entry. Since I was testing
the database I deleted the test records and did a compact and repair the
database.
Yet the number is still showing the next incremental number instead of
starting back at 1.

thanks

 

[S Panja]

Later versions of Access do not reset AutoNumber to 1 on compaction.
--
********* http://panjas.org

http://www.linkedin.com/in/spanja

If the reply is helpful or is the answer, click Yes next to
Was this post helpful to you?
or
Did this post answer the question?

 

[Ken Sheridan]

If maintaining sequential values is important then don't use an autonumber;
its only designed to guarantee uniqueness not sequence. By using a
straightforward long integer number as the key you can generate the next
number in a bound form's BeforeInsert event procedure with:

YourKey = Nz(DMax("YourKey", "YourTable"),0)+1

This is fine in a single user environment, but conflicts could arise in a
multi-user environment on a network. This would produce a data error (a
violation of the unique index) which could be handled in the form's Error
event procedure. Alternatively you can avoid the error by only allowing one
user to get a number simultaneously. You'll find an example of how this can
be done, and also of how to rest the next number at which the sequence will
start, at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Dirk Goldgar]

Later versions of Access do not reset AutoNumber to 1 on compaction.

Actually, most of them do. There was a particular Jet version that didn't.
I'm guessing, though, that Rpettis31 has that version.

 

[Rpettis31]

I have been working on this database for sometimes and I remember previously
I was able to delete the test records and compact and repair the database and
the numbers reset. I am using 2003.

 

[RiRi]

I found this!
http://support.microsoft.com/kb/812718

R