Duplicated autonumbers

[Andrew Smith]

The autonumber in one of my tables (set to increment) has started to reuse
existing numbers. This has caused problems for the users who were unable to
enter records, and for me as I've spent ages tracking down the problem.

Once I identified what the problem was, I found that it has supposedly been
fixed in Jet SP7, so I installed this on all machines using the database. It
made no difference at all - duplicate autonumbers continue to be generated.

A search on Google threw up a couple of recent threads on this newsgroup
about the same problem, both started by the same person. This does not seem
to me to be much discussion about something that seems a quite serious flaw.
Have others also suffered with this problem, or is it really only affecting
a couple of us?

I've now changed the autonumber to random as this was the only solution that
I could see. However, how can I be sure that the random autonumbers will not
repeat at some point?

 

[Allen Browne]

JET 4 SP7 or 8 is supposed to fix the cause of the problem, but if you
already have the problem, the Seed of your AutoNumber fields need to be
changed to a suitable value. There's a piece of code here to do that:
http://allenbrowne.com/ser-40.html

Some users seem to see this more than others. We see it quite rarely: last
time was a client who had updated from Access 2 to 2000.

 

[Mike Sherrill]

The autonumber in one of my tables (set to increment) has started to reuse
existing numbers. This has caused problems for the users who were unable to
enter records, and for me as I've spent ages tracking down the problem.

Once I identified what the problem was, I found that it has supposedly been
fixed in Jet SP7, so I installed this on all machines using the database. It
made no difference at all - duplicate autonumbers continue to be generated.

In earlier SPs, some users reported the same problem. Try this: using
a machine that is already patched to SP7, create a new MDB, and import
all the objects from the dodgy MDB.

No guarantees. Let us know.

 

[Andrew Smith]

Thanks for the suggestion.

It's a bit academic now as I've change the autonumber to "random" on the
database that's in use, but I could try your idea on a backup just for
interest.

 

[Andrew Smith]

Thanks for the information.

I have not run this code, so perhaps this is the reason why the patch did
not seem to work. However, the autonumbers always seemed to be out by about
5, so it was fairly easy to try adding records until a new number was
generated. I did this after installing the patch, but the problem still
reappeared.

Anyway, it's a bit academic now as I've changed to random autonumbers on the
problem table, and I'm inclined to use random numbers for everything in the
future.

JET 4 SP7 or 8 is supposed to fix the cause of the problem, but if you
already have the problem, the Seed of your AutoNumber fields need to be
changed to a suitable value. There's a piece of code here to do that:
http://allenbrowne.com/ser-40.html

Some users seem to see this more than others. We see it quite rarely: last
time was a client who had updated from Access 2 to 2000.

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

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

The autonumber in one of my tables (set to increment) has started to reuse
existing numbers. This has caused problems for the users who were unable to
enter records, and for me as I've spent ages tracking down the problem.

Once I identified what the problem was, I found that it has supposedly been
fixed in Jet SP7, so I installed this on all machines using the

database.