AutoNumber Field Broken: How To Fix?

[(PeteCresswell)]

Got a table of bond ratings.

273 rows.

First column is called "RatingID", is an AutoNumber field and is
the PrimaryKey.

But when I go to add a new row, RatingID gets populated with "96"
- the value of an existing row's RatingID.

Seems like the seed for AutoNumber - wherever it is - has been
corrupted.

Does anybody know a safe way to fix it?

Creating a new field will not work bc some records have been
deleted and renumbering by one from one will mess up existing
relationships.

 

[John W. Vinson]

Got a table of bond ratings.

273 rows.

First column is called "RatingID", is an AutoNumber field and is
the PrimaryKey.

But when I go to add a new row, RatingID gets populated with "96"
- the value of an existing row's RatingID.

Seems like the seed for AutoNumber - wherever it is - has been
corrupted.

Does anybody know a safe way to fix it?

Creating a new field will not work bc some records have been
deleted and renumbering by one from one will mess up existing
relationships.

Run a little Append query:

INSERT INTO yourtable (RatingID) VALUES(274)

or whatever you want as the next RatingID. This should reset the seed; you can
then delete the added row and it will pick up again with 275.

You should also compact and repair the database, and make sure you have all
the patches available for your version of Access.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Tony Toews]

But when I go to add a new row, RatingID gets populated with "96"
- the value of an existing row's RatingID.

Seems like the seed for AutoNumber - wherever it is - has been
corrupted.

The excellent John Vinson has one solution. But what really puzzles
me is how this happened in the first place. I haven't seen mention of
this problem in years. There was a Jet 4.0 SP quite a while back that
caused this but it's been a number of years now. Besides that SP is
updated with the Windows OS so if you're up to date you should never
have seen this problem.

http://support.microsoft.com/?kbid=257408
http://support.microsoft.com/?kbid=291162

But those articles have long been made obsolete because the SPs have
long been included in Windows SPs.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[David W. Fenton]

The excellent John Vinson has one solution. But what really
puzzles me is how this happened in the first place.

You can very easily do it by inserting a record to fill a gap in
your autonumber. I recently did this with a client app where we were
recreating some lost data that had somehow been erroneously deleted
in order that referential integrity could be restored. So, I was
inserting Autonumber values to fill in gaps. This reset the seed
value, and I had to reset it back to the correct value (I can't
recall whether I did it the easy way, which would sacrifice a value,
or if I did it the right way, which would set the seed value in
code).

I haven't seen mention of
this problem in years. There was a Jet 4.0 SP quite a while back
that caused this but it's been a number of years now. Besides
that SP is updated with the Windows OS so if you're up to date you
should never have seen this problem.

http://support.microsoft.com/?kbid=257408
http://support.microsoft.com/?kbid=291162

But those articles have long been made obsolete because the SPs
have long been included in Windows SPs.

I would not expect it to happen spontaneously any more. I haven't
seen that one since before Jet 4 SP6 (which was the one that finally
fixed it).

But it's possible it's a sign of some form of corruption. If I had
an app where it was happening spontaneously (i.e., not because of
filling gaps intentionally), I'd start worrying that I had a flakey
network or an unreliable software environment.

 

[(PeteCresswell)]

Per Tony Toews:

The excellent John Vinson has one solution. But what really puzzles
me is how this happened in the first place.

It was in a copy of the production database that had undergone
cruel and unusual treatment.

The application in question has been replaced by a .NET app for
99% of the funds being tracked, but one user likes this app so
much she refuses to manage her funds with the new one.

So I'm paring the DB down to just the two funds she manages.
The problem cropped up after I'd tested a suite of queries (the
genesis of the "Running Query In VBA, Bypassing Warnings?" that I
started last week) whose purpose was to carve out all that
obsolete data..... this is a *lot* of records in many tables.

FWIW, after I implemented John's fix, the new number became "0"
(as in zero...) instead of the faulty number what was from
somewhere in the middle.

I hope that, after the next test, the problem will not recur.
If it turns out tb replicable, that's one thing.... otherwise
I'll just breathe a sigh of relief and get on with it....

 

[frank]

Per Tony Toews:


It was in a copy of the production database that had undergone
cruel and unusual treatment.

The application in question has been replaced by a .NET app for
99% of the funds being tracked, but one user likes this app so
much she refuses to manage her funds with the new one.

So I'm paring the DB down to just the two funds she manages.
The problem cropped up after I'd tested a suite of queries (the
genesis of the "Running Query In VBA, Bypassing Warnings?" that I
started last week) whose purpose was to carve out all that
obsolete data..... this is a *lot* of records in many tables.

FWIW, after I implemented John's fix, the new number became "0"
(as in zero...) instead of the faulty number what was from
somewhere in the middle.

I hope that, after the next test, the problem will not recur.
If it turns out tb replicable, that's one thing.... otherwise
I'll just breathe a sigh of relief and get on with it....

The same thing happened to me a couple of weeks ago. I am using 2003
with SP 3. I solved it by deleting the contents of the table and
reinserting the records.
I don't know how it happened. I started with a table full of records
and then made many design changes over time including renaming the
table, changing the design of primary keys, deleting and recreating
relationships, etc... At some point it started creating duplicate ids.
Deleting and reinserting everything worked great and there have been
no problems since.

 

[(PeteCresswell)]

Per frank:

I solved it by deleting the contents of the table and
reinserting the records.

I think this uncovers a downside of using AutoNumbers for a PK.

 

[Tony Toews]

You can very easily do it by inserting a record to fill a gap in
your autonumber. I recently did this with a client app where we were
recreating some lost data that had somehow been erroneously deleted
in order that referential integrity could be restored. So, I was
inserting Autonumber values to fill in gaps. This reset the seed
value, and I had to reset it back to the correct value (I can't
recall whether I did it the easy way, which would sacrifice a value,
or if I did it the right way, which would set the seed value in
code).

Oh, now that's interesting. I can't recall the last time I did that.
Definitely a bug somewhere then.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[David W. Fenton]

Per frank:

I think this uncovers a downside of using AutoNumbers for a PK.

???

This is not a new problem.

It first popped up in Jet 4 shortly after it was released because
Jet 4 was the first version to allow programmatic control of the
Autonumber seed. It was not until Jet 4 SP6 that it was fixed
permanently. My copies of SP6 are from 2002, so the main issue has
been solved for EIGHT YEARS -- I've not seen a single spontaneous
loss of Autonumber seed since SP6, but I did see it more than once
in Jet 4 before SP6.

Now, by exposing the Autonumber seed value to programmatic control,
it did change the way Autonumbers work when you insert data that has
Autonumber values less than the highest value in the field, and
that's annoying, but it's not demonstrably wrong behavior, just
DIFFERENT from how it behaved in the past.

So, I really don't think there's any reason to think there's
something unreliable about Autonumbers. They work slightly
differently then they did before Jet 4 was introduced (more than 10
years ago, I might add), but the change in behavior is arguably a
valid way for things to work.

And the fix is just the same method we had for setting the
Autonumber seed value pre-Jet 4.

Thus, I really think there's not need to get hysterical about it.

 

[rumkus]

AutoNumber Field Broken