Access 2002 behavior in a multi-user environment

[waterman]

The Problem: I seem to be having trouble with Access 2002
assigning the same Autonumber (set for no duplicates)to
multiple entries in a multi-user environment.

I've had this database around for several months now, but
this problem just started happening last week.

The Set-Up:

- The database is split, and the front-ends are loaded
on the individual desk tops.
- The backend resides on a 2000 server.
- When two users attempt to enter a record at the same
time, they receive the same autonumber.
- the first user to complete their entry and move on to
the next record gets the number.
- the second user receives a message about the
autonumber not being unique, and must shutdown the
database to escape the conflict

The Question: What can I do to prevent this from
happening?

 

[Cheryl Fischer]

Here is a link with more info; perhaps it will help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;291162

 

[david epsom dot com dot au]

The Problem: I seem to be having trouble with Access 2002

assigning the same Autonumber (set for no duplicates)to
multiple entries in a multi-user environment.

It can take several seconds for data to propagate through
the caches from one user to another. Specifically, both
users may think that '7' is the next free autonumber, but
only one will succeed in using it.

A solution is discussed in this article:

HOWTO: Implement Multi-user Custom Counters in DAO 3.5
http://support.microsoft.com/default.aspx?scid=191253

The article refers specifically to DAO 3.5, and suggests
using DAO methods rather than registry settings for the
Jet Engine. You will probably find that the DAO methods
for handling the Jet cache don't affect Forms in A2K+,
so it may be better to use the Jet registry settings or
equivalent engine options.

this problem just started happening last week.

That is normal. Don't know why.

- The backend resides on a 2000 server.

The whole system was built originally for Win 3.x
When you use Access 2002 and Win 2K, it is slower.
The network optimisations in Win2K only make Access
slower, the superstructure has expanded enormously.
and Access may be the only product left on the market
that uses the native database api of the file system.

(david)