MS Access Multiuser - Strange network share experiences...

Author

Strange network share experiences...

JamesC

2006-01-23, 9:24 am

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

david epsom dot com dot au

2006-01-23, 8:25 pm

Access is inextricably linked to the file system. The
file system is an integral part of the Access /Jet database
engine. Access uses the database primitives provided
by the file system, which are not used by other, non
database, programs. Microsoft Access uses the Microsoft
file system using features defined by Microsoft.

Other file systems are not supported. Only a few other
file systems have ever been certified.

Back in the dark ages, MS used to document the record
locking behaviour of the Access database system. That
is no longer true.

If Access doesn't work on your SAN, don't use Access,
or don't use the SAN. (MS doesn't really care which).

However, from the description of your problem, you might
find a fix by disabling Opportunistic Locking on the
workstation network clients.

Google for Opportunistic Locking. Look specifically
for how to turn it off at the client. It can also
be disabled at the SAN, but only if you have good
documentation.

(david)

"JamesC" <u17973@uwe> wrote in message news:5aca6cc5b9006@u
we...
> 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

Tony Toews

2006-01-23, 8:25 pm

"JamesC" <u17973@uwe> wrote:

>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!

I agree with David. The problem is that MS uses "phantom locks" in a
non standard method. So it may work well and may not.

ACC2000: Microsoft Access and Untested Networks - 209161
http://support.microsoft.com/?kbid=209161

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

JamesC via AccessMonster.com

2006-01-24, 7:24 am

Thanks for your thoughts and comments...

.... it's ringing distant bells, but I'm not sure why I've never experienced
it before.

I've done some further reading on Opportunistic Locking, and Read Caching. We
will probably attempt to turn this off on the Server - if we do that, do we
then have to turn it off at the Workstation level as well?

Ultimately, the medium term solution will be to haul this all into SQL server.

Thanks again!

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...tiuser/200601/1

david epsom dot com dot au

2006-01-24, 8:25 pm

"JamesC via webservertalk.com" <u17973@uwe> wrote in message
news:5ad57001e4b3c@u
we...
> Thanks for your thoughts and comments...
>
> ... it's ringing distant bells, but I'm not sure why I've never
> experienced
> it before.
>
> I've done some further reading on Opportunistic Locking, and Read Caching.
> We
> will probably attempt to turn this off on the Server - if we do that, do
> we
> then have to turn it off at the Workstation level as well?

Not if it works correctly. Workstations should not cache
data without permission from the file server.

But since we already know that it does NOT work correctly,
all bets are off.

Access/Jet normally asks for shared access. The client
is opportunisticlly asking for exclusive access, which
permits local caching. The server should be upgrading the
locking to opportunistically exclusive, but demanding
the cache data back if there is a share request from
another client.

On some systems you can just label the file as shared,
which prevents exclusive access, which then prevents
client caching. In this case, I would expect file corruption
if you still got exclusive access to the ldb lock file,
which is a temporary file unless you remove delete permission.

Preventing file caching on the server should also have
the effect of preventing caching on the clients if the
two are communicating correctly.

Preventing file caching on the client should also solve
the problem unless the server somehow caches the clients
seperately.

>
> Ultimately, the medium term solution will be to haul this all into SQL
> server.
>

See! Microsoft wins either way :~)

(david)

>
> Thanks again!
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...tiuser/200601/1

Ian Davies

2006-01-30, 8:25 pm

>
> Ultimately, the medium term solution will be to haul this all into SQL
> server.
>

See! Microsoft wins either way :~)

(david)

use mysql
Then they won't
Ian

"david epsom dot com dot au" < david@epsomdotcomdot
au> wrote in message
news:uK9FqKTIGHA.3896@TK2MSFTNGP15.phx.gbl...
>
> "JamesC via webservertalk.com" <u17973@uwe> wrote in message
> news:5ad57001e4b3c@u
we...
Caching.[color=darkred]
>
> Not if it works correctly. Workstations should not cache
> data without permission from the file server.
>
> But since we already know that it does NOT work correctly,
> all bets are off.
>
> Access/Jet normally asks for shared access. The client
> is opportunisticlly asking for exclusive access, which
> permits local caching. The server should be upgrading the
> locking to opportunistically exclusive, but demanding
> the cache data back if there is a share request from
> another client.
>
> On some systems you can just label the file as shared,
> which prevents exclusive access, which then prevents
> client caching. In this case, I would expect file corruption
> if you still got exclusive access to the ldb lock file,
> which is a temporary file unless you remove delete permission.
>
> Preventing file caching on the server should also have
> the effect of preventing caching on the clients if the
> two are communicating correctly.
>
> Preventing file caching on the client should also solve
> the problem unless the server somehow caches the clients
> seperately.
>
>
>
> See! Microsoft wins either way :~)
>
> (david)
>
>
>
>

Sponsored Links