Dear All,

I'm a new member of this forum, so as well as asking a question in my first
post, I'd better say hello to you all.

I've been developing databases and applications in MS Access for 10 years now,
and have experienced every version since Access 2.0. Fortunately we're not
stuck in the dark ages any more, and life is relatively easy.

I would like to tell you about a situation I have encountered recently, and
see if any of the more experienced among you had any knowledge of it. It's
certainly a first for me, despite being extremely familiar.

Part of my current role is to pick up, assess, tidy and repair some Legacy
Access appilcations. The one I'm going to talk about is nothing complicated
at all - a simple back-end database consisting of a dozen tables, a very
simple data structure, and a front-end client application. Originally this
was all held in Access 97, however I have recently moved this to a compiled
MDE, which is being run via the ART 2003.

Importantly, the back end data has been migrated to a SAN device (Storage
Area Network), as part of a significant network project upgrading the
company's infrastructure.

The application itself is a Purchase Order tracking database. Purchase orders
are held over several tables, containing header and line level information.
The actual Purchase Order number is a processed calculation via a function I
have written using a method that is tried and tested over the years. A single
field holds the next PO number; the function then pessimistically locks this
field, reads the number, and updates it. Only when the record is committed
and verified as having updated, is the obtained PO number allowed to be used
on a new record. Should the process fail, it is started again from the
beginning.

This is important, as it is just as reliable as the Autonumber identifier on
each of the data tables.

The problem I have is this:  A user may have successfully entered records
into the database - the PO number has been incremented, as have the table
Autonumber fields. The data has as far as I can tell, been commited to the
MDB file. Then, a while later, after the first user has logged off, another
user may come along and start inputting their Purchase Orders... however,
they start using the same PO numbers, and Autonumbers as the first user, as
if the first user had never been there.

To demonstrate this, I have two copies of the same database, taken a couple
of hours apart. The second, and later copy, has fewer records in than the
first, indicating some missing data, however, the PO number and Autonumbers
remain in the correct sequence.

The symptom is as if someone had replaced the database with a copy of an
earlier one - however I don't think this is actually something that someone
has intentionally done, and not on the multiple occasions that it has been an
issue.

For more of the background, the SAN is replicated with an identical SAN at
our Disaster Recovery site, however this is a one way process, there is,
apparently, no way that old data can be replicated back to the live data,
overwriting my data.

I have recently moved the back-end data to another server, away from our SAN,
and two weeks later the problem has not repeated itself. However, that may
just be coincedence.

I'm hoping that I can apeal to the broad experiences of everyone here, and
get some ideas and some discussion flowing!

Many thanks

James