Autonumber field behavior

[M]

Hello. I am in the process of diagnosing and correcting an Access 2002/XP
database error. I have noticed that an Autonumber field used to hold a
PurchaseOrderID (that is its name) recently "jumped" from 15000 to 2800000,
that is from 15 thousand to nearly three (3) million. What conditions or
events might cause for such a jump in the internal value of the Autonumber
field?

A repair and compact produces no improvement or reports any errors.

Thanks in Advance for any feedback.

 

[Roger Carlson]

An autonumber field should never be used to hold data with meaning. The
user should never care what the value is. The problem with autonumber is
that you do not have any control over its value and it is DESIGNED to be
that way. It is very easy to loose a number in the sequence. All you have
to do is start a new record and then cancel without saving. That number
will be gone forever.

As to HOW it happened, that's hard to say exactly, but a simple Append query
where you attempt to set the autonumber will do it. Suppose we had a table
"Authors" with an autonumber field AuID. If I run the following query:
INSERT INTO Authors ( AuID ) VALUES (99999999);
It will not append the record, but the autonumber will start at 100000000.

In Access 97, you could just delete the higher records and Compact and that
would reset the autonumber, but that does not work in Access 2000+.

If you must have a sequential number without gaps, you should code your own.

On my website is a small sample database called "AutonumberProblem.mdb"
which explains more and illustrates a solution.