Autonumber weirdness

[Guest]

I have a table with an autonumber key. At the end of May 9th, the last key
was 13,337. At the beginning of the next day, the first key was 866,199,597.

What might possibly have caused this, and how can I prevent it in the future?

Kevin

 

[David C. Holley]

If there were any append & delete queries executed that would explain
it. Otherwise, no idea.

I would ask though, do you have 14,000 records in the db? If so you may
want to start looking at SQLServer for performance reasons.

 

[Rick Brandt]

I have a table with an autonumber key. At the end of May 9th, the
last key was 13,337. At the beginning of the next day, the first key
was 866,199,597.

What might possibly have caused this, and how can I prevent it in the
future?

Kevin

In the newer Jet versions (Access 2000 and higher) there were some early
problems with AutoNumber that sometimes caused the seed number to change.
Worse than your situation was when the seed value would be reset to a range
that was already used and then duplicate key errors would result. Later
service packs dealt with the duplicate problem but I believe that MS still
only guarantees "Uniqueness" for new AutoNumbers. There are apparently
still sitiuations that cause the big jumps that you have seen.

Now the standard caveat is "If you care about the value in any way other
than uniqueness, then don't use an AutoNumber". Beyond the issues mentioned
above there have always been completely normal activities in Access that
would create gaps in an AutoNumber sequence. For example if you create a
Append query and cancel at the prompt...

"You are about to append 5000 records..."

....the 5000 AutoNumber positions that the query was going to use are already
consumed by the time that prompt is displayed. That being that case your
jump could theoretically have been caused by something normal like this.
It's just that a jump that severe is more likely to be from something
not-quite-right.

 

[Rick Brandt]

If there were any append & delete queries executed that would explain
it. Otherwise, no idea.

I would ask though, do you have 14,000 records in the db? If so you
may want to start looking at SQLServer for performance reasons.

Are you serious? With a couple more zeros added to the number maybe.
14,000 is teeny.

 

[Guest]

Actually, I've been bitten by the "reuse an old number" bug once, too, a
couple of months ago. I though I had the new Jet versions installed
everywhere, but maybe I have to double-check.

I've been relying on statements I'd heard early on (4 or 5 years ago) that
AutoNumber was reliable, and I use them heavily as foreign keys in related
files. This is starting to scare the sh*t out of me.

When the "reuse" number problem bit me it almost got me fired. Wonder why
that doesn't happen to the MS programmers?

Kevin

 

[Rick Brandt]

Actually, I've been bitten by the "reuse an old number" bug once,
too, a couple of months ago. I though I had the new Jet versions
installed everywhere, but maybe I have to double-check.

I've been relying on statements I'd heard early on (4 or 5 years ago)
that AutoNumber was reliable, and I use them heavily as foreign keys
in related files. This is starting to scare the sh*t out of me. [snip]

With all of the Service Packs installed they ARE reliable as long as the only
thing you are relying on is uniqueness. In the case of fields for relationships
between tables all you need is uniqueness. The actual value should not matter
at all.

 

[Guest]

Thanks for the assurance, Rick. Appreciate. Now I just have to reset and
repopulate the jumped Autonumbers, and I know how to do that.

Kevin

Actually, I've been bitten by the "reuse an old number" bug once,
too, a couple of months ago. I though I had the new Jet versions
installed everywhere, but maybe I have to double-check.

I've been relying on statements I'd heard early on (4 or 5 years ago)
that AutoNumber was reliable, and I use them heavily as foreign keys
in related files. This is starting to scare the sh*t out of me. [snip]

With all of the Service Packs installed they ARE reliable as long as the only
thing you are relying on is uniqueness. In the case of fields for relationships
between tables all you need is uniqueness. The actual value should not matter
at all.

 

[Allen Browne]

in message

... 14,000 records in the db? ... may want to start looking
at SQLServer for performance reasons.

LOL. Obviously written by someone with no experience in Access.

Or perhaps he misread and thought there was another 2 zeros on the end.

 

[David C. Holley]

Well for that matter 14,000,000 is small compared to 14,000,000,000,000,000

 

[David C. Holley]

As an alternative to using AN for unique keys, building a key based on
Now() usually works. key = CDbl(Now()). If you're concerned about the
number of users working, you can add a UserId/Password to the DB and
then using a key like key = CDBL(Now()) & txtUserId.

 

[David C. Holley]

No, just never dealt with 14,000 records in a DB and having heard
multiple stories about Access crapping out when the number of recs gets
too high.

 

[Alex White MCDBA MCSE]

Beautiful!!!

 

[David C. Holley]

No, its gorgeous because if the userID who created the record is
captured in the key, you have an additional audit trail.

 

[David C. Holley]

No its utterly gorgeous because if the key is comprised of the date,
time & userID who added the record you can extract the information back
out if needed - and only at a minium cost.

 

[John Spencer (MVP)]

Well, I've got Access tables with more than a million records in one table and
have not had any flaky behavior yet. The database has many other tables also.
Right now it is around 450 megs in size.

 

[Alex White MCDBA MCSE]

Agreed!!!!!!!

 

[Allen Browne]

Actually, any key involving a date/time value in Access is subject to the
problems associated with floating point numbers, not to mention the
additional problems of correctly interpreting the time value for negative
dates, so generally not a good idea.

 

[Alex White MCDBA MCSE]

Hi Allen,

I see your point the date being the left side of the number (whole number)
and the time being the right side, so how about moving the decimal place? I
know the number would be large, but solution would elegant if it was without
this problem.

 

[David C. Holley]

What problems would that be?

Also, what do you mean by a 'negative' date? CDbl(Now()) would never
create a negative number since its based on the current date and time.

 

[Allen Browne]

David, I'm sure you are aware of the matching problems associated with
floating point numbers.

Regarding the negative values, Access stores date/times as a strange data
type where the date is represented by an integer, and the time as a fraction
of a day. That works pretty much as expected for dates from 1900 onwards,
but yields very strange math for dates prior to that. For example, you might
expect there to be 1/24 of a day between 11pm Dec 29 1899 and Dec 30 1899,
but it does not work like that:
? CDbl(#12/30/1899#) - CDbl(#12/29/1899 11pm#)

At one time I thought this would be unlikely to ever be an issue for anyone
I was working with, but that has not proved to be the case.