Duplicate autonumbers

[Ngan Bui]

This has happened a couple times before. We have Office
XP, jet 4.0 SP8. User 1 goes and creates a new record and
gets a new autonumber (6611). He works on the record for
awhile and haven't saved it. User 2, in the mean time,
goes and creates a new record too and somehow gets the
same autonumber (6611). She saves the record fine.

When User goes to save his record, he gets an error saying
that he can't have a duplicate primary key. He would have
to undo his changes and re-do the new record to get a new
autonumber.

I can't recreate this problem because when I try, I do get
different numbers. I know that Access doesn't dup
autonumbers regardless of if you save a record or not.

Can someone explain why this happens?

 

[david epsom dot com dot au]

This can happen only when you have multiple workstations.
The data is cached locally in the Jet Cache (and, if you
have problems with Windows, in the network client cache).

The copy in your cache says that the next autonumber is '5'.
You start an new record, and your cache says the next
autonumber is '6'

He starts a new record, and the copy in his cache still
says '5', then '6'. If he had waited a couple of minutes,
(it is supposed to be only seconds, but, does it work
correctly?), then your cache would have written back
to the server, and his cache would have refreshed from
the server, and he would be on 6/7 instead of 5/6.

1) Jet is a multi-user distributed database engine, not
a real-time network communication device. There are
inherent limitations (and advantages!).

2) Jet is a distributed client-server database engine,
not a server database engine. There are inherent
limitations (and advantages!).

If you want to work around these limitations, you can

a) use a multi-field primary key, with a workstation
ID field.

b) always refresh the cache before creating a new record
Application.dbEngine idle 8

c) save a record quickly, then update it from the form,
rather than holding a new record open for data-entry.

d) Create your own high-concurrency custom counter:
HOWTO: Implement Multi-user Custom Counters in DAO 3.5
http://support.microsoft.com/default.aspx?scid=kb;en-us;191253

FWIW, Autonumber has never really been suitable for situations
like this, but because of the problems that have be-devilled
Autonumber over the last 4 years, I would say that Autonumber
is not suitable for any situation :~)

(david)

 

[Ngan Bui]

Thanks for the answer!

For choice b, where would I put that code?

In the next year, I plan on moving everything to SQL
server. If I want to continue using autonumbers as PK,
will I run into this problem with sql? Say that I
use .adp files or webpages instead of mdbs.

Thanks.
Ngan

 

[david epsom dot com dot au]

No problems using autonumbers (identity fields) with
SQL Server, unless you try to use A97 to write a value
into an identity field (you probably won't want to do
that).

For choice (b), probably best to change your forms to
PREVENT new records, then add a 'New Record' button
that adds a new record and takes you to it. You can
refresh the cache in the 'New Record' code.

But you can just add that code anywhere: in the after
update event if you are doing data entry, in the form
load event if you enter one record at a time.

(david)