Autonumber fields gone haywire

[Stephen Raftery]

Hi all, can anyone help with a wierd problem?

I have been using a database for soem years, which involves a number of
related tables. I have a table of Transactions which contains an autonumber
TransactionID, plus several other fields. There is also a table of Items,
which has an autonumber ItemID, and TransactionID which is a foreign key
pointing to the forst table.

What is happening now is that when I enter a new record, the autonumber does
not increment properly: it used to increment to the next higher number above
those already in teh table, but now it is filling in numbers of records that
have been deleted ages ago.

I have been able to temporarily fix it by Appending a junk table with just
the next autonumber value that I want it to have, but after a few minutes it
reverts to its odd behaviour.

The problem started when I installed the MSDE program, which I have since
removed, and re-installed the whole Access program, and created a new
database into which I imported all the structures and data from the old
database. All to no avail.

Does anyone know how I can fix this?

Stephen


Stephen

 

[TedMi]

If you care about the value of an autonumber, then most likely an autonumber
is not what you want.

 

[Stephen Raftery]

The thing is, I don't care about its value: i only care that it is unique and
never repeated. For some reason, the program has started to repeat
autonumbers.
Any ideas how I can stop this?

Stephen

 

[Allen Browne]

There are several situations that can trigger this.

See:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Rick Brandt]

The thing is, I don't care about its value: i only care that it is
unique and never repeated. For some reason, the program has started
to repeat autonumbers.
Any ideas how I can stop this?

Have you tried compact and repair? Have you tried importing all objects into a
new file? Do you have all Access and Jet service packs installed?

 

[Stephen Raftery]

I have checked that my Windows XP SP2 is installed.

Stephen

 

[Stephen Raftery]

Thanks, Allen,

That routine works just fine for fixing the haywire autonumbers. But the
problem then recurs every time I use VB to append any new records.
My VB code makes no attempt whatever to write to any autonumber field.
However, when the new record is created, the autonumber field used is not an
incremented one, but one from a previously deleted record.

I have recompiled, compacted and imported all my objects and data from the
old version of the database to the new one.

Stephen

 

[David F Cox]

Access is at fault because it should allow you to choose to set up an
autonumber to increment by one, and then use some other algorithm for its
selection of the next number. There have been over 20,000 posts mentioning
problems and autonumber on the newsgroups, and who knows how many unrecorded
here. We can either dream about a party action against Microsoft or get on
with our lives.
Given that the increment by one option is misleading, and that you have been
misled, we have to get on with it.
Access is generating a unique record identifier within the table. If you are
using the same number elsewhere that is your responsibility. You cannot
expect Access to know that you are using a number that you have discarded on
this table for some other purpose in some other place.

You are going to have to generate your own keys to match your own
requirements. Using DMAX to find the last number used and adding one is a
common solution to an all too common problem. I think that Microsoft has
been negligent in either not fixing this problem or in not fixing its
documentation. If I pick an "increment" option, that is what I would expect
to get.

 

[TedMi]

If Autonumbers are assigned the same number as deleted records, does that
make a difference? You should only care if it assigns a number that currently
exists in the table, because that would prevent entry of new records when the
autonumber is the PK (and I can't think of any other for Autonum).

 

[David W. Fenton]

Access is at fault because it should allow you to choose to set up
an autonumber to increment by one,

An AutoNumber has two possible values:

1. increment

2. random

Increment is what most people will use, but a Random AutoNumber is
essential in replicated databases to minimize the possibility of PK
collisions. There's also the ReplicationID, but that's not
recommended for use in any circumstances, as Access doesn't get
along well with it (see
http://www.trigeminal.com/usenet/usenet011.asp for an explanation of
what kinds of problems GUIDs cause when used in Access).

and then use some other algorithm for its
selection of the next number. There have been over 20,000 posts
mentioning problems and autonumber on the newsgroups, and who
knows how many unrecorded here. We can either dream about a party
action against Microsoft or get on with our lives.

Jet 4 introduced for the first time the ability to programmatically
manipulate the seed value that determines the next number assigned,
and there were terrible bugs in it. Until Service Pack 6, it was
very common for the seed value to get corrupted and produce
duplicate values. Of course, with a PK, this did not result in bad
data, since the PK's unique index would prevent it, but it would
also prevent the addition of new records.

I have not seen this problem since Jet SP6, however.

But it was very annoying back then.

Given that the increment by one option is misleading, and that you
have been misled, we have to get on with it.

It's important to *not* think of an AutoNumber as a unique
identifier at all. It's not -- it's just a special form of default
value, no different than if you put Date() in a date field's default
value so that when the record was created the Date would be
inserted. In the case of an incrementing AutoNumber field, the
default value is Max() + 1, and the Random is some variation on:

(Rnd() * 4.294967296 * 10^9) - (2.147483648 * 10^9)

(I'm certain it's something hardwired to be more efficient than
that, but if you test that in the immediate window, it produces
values in the same range as the Random Autonumber)

A simpler way to implement it would be:

Rnd() * 4.294967296 * 10^9 * Sgn(Rnd()-.5)

But that also involves to calls to Rnd().

The point is that you can't think of the Autonumber as a record
identifier. It is not. It is a default value. That is why you can
append data to an Autonumber field in a new record, because it's
populated by a hidden default value, but can be any value at the
time the record is created. The only difference from regular fields
with default values is that you cannot change the value of the
AutoNumber once it's been set.

Access is generating a unique record identifier within the table.

No, it's not. It's populating the field according to an algorithm
that runs at record creation.

If you are
using the same number elsewhere that is your responsibility. You
cannot expect Access to know that you are using a number that you
have discarded on this table for some other purpose in some other
place.

Huh?

This is a bug in Jet caused by problems in the Autonumber seed
value. It's just a bug, one that was fixed in later Jet service
packs.

You are going to have to generate your own keys to match your own
requirements. Using DMAX to find the last number used and adding
one is a common solution to an all too common problem. I think
that Microsoft has been negligent in either not fixing this
problem or in not fixing its documentation. If I pick an
"increment" option, that is what I would expect to get.

It's a bug in Jet. It has been fixed.

What part of that is not clear?

 

[Joan Wild]

You also need to ensure you are uptodate with Office and Jet service packs.
If you locate msjet40.dll in the windows system folder, right-click it and
choose properties. The version tab should have 4.0.8618.0 as the file
version. If your version isn't that then you need to update Jet (Windows
Update should provide it for you).

If you open any Office program and go to the Help..About menu, that dialog
will tell you if you have the latest service packs installed. Depending on
version:
Access 2003 (11.6566.8036) SP2
Access 2002 (10.6501.6811) SP3

If you aren't uptodate, there is an item in the Help menu that will take you
to the update page at microsoft.com

 

[Ray Cacciatore]

I had a similar problem and simply getting the updated version of Jet does
NOT solve the problem. The reason being that with the new version of Jet the
problem will never reoccur only with new tables you create from that point
on. However, if you created the faulty table with the old Jet version, the
problem will always be there (even if you install a new Jet version).

I had exactly the same problem months ago, I installed a new version of Jet
and the problem kept reappearing every time I compacted the database. What I
had to do is install the new version of Jet, then I recreated the autonumber
field. Yup, I deleted the old one and recreated the autonumber field. Luckily
it was a primary key and not a foreign key.

Ray

 

[Ray Cacciatore]

I had a similar problem and simply getting the updated version of Jet does
NOT solve the problem. The reason being that with the new version of Jet the
problem will never reoccur only with new tables you create from that point
on. However, if you created the faulty table with the old Jet version, the
problem will always be there (even if you install a new Jet version).

I had exactly the same problem months ago, I installed a new version of Jet
and the problem kept reappearing every time I compacted the database. What I
had to do is install the new version of Jet, then I recreated the autonumber
field. Yup, I deleted the old one and recreated the autonumber field. Luckily
it was a primary key and not a foreign key.

 

[Joan Wild]

True enough Ray. The updated Jet will ensure it won't happen in future.
Allen Browne referred to a page on his site to fix the issue (see up
thread).