Data Locking in Shared DB

[Scott]

I would appreciate suggestions on the following problem....

I am having problems sharing a database that is stored on a network
drive. The problems being when a second user tries to share use of the
mdb file over the network.

There are two errors:
--Sometimes any user after the first will get a series of warning
messages... each saying the same thing,
"You do not have exclusive access to the database at this time.
If you
proceed to make changes, you may not be able to save them
later."
The user must click through about ten of these before the system lets
them proceed.

--Even if the second (or greater) user doesn't get these messages, the
system may not lock the records. If a second user has connected,
any user trying to use the DB will get an error message when trying
to add a new record.
The system responds with a message that says,
"Save Action Cancelled."

Background:
-- The db is an Access 2003 mdb that has linked tables.
The linked tables are stored on a SQL server. (I know it would be
better as an ADP, but I am upgrading system and I am not able to
convert it to a project. )

-- I have the database's "default record locking" set to "Edited
Record."
-- I have each form's "Record Locks" property set to "Edited
Record."
-- I all forms that only display data to have "Recordset Type" of
"Snapshot."
-- Forms that edit records are set to "Dynaset."
-- The database opens in shared mode.

The problem does NOT occur if users copy the mdb file to their desktop
and run from there, but always occurs if two or more users run the file
from the network location.

Can someone point out what I am missing or should try?

Thanks!

 

[Scott]

Sorry.. the second error should read, "the system WILL lock the
records,"
instead of "may not lock."

 

[Douglas J. Steele]

I would appreciate suggestions on the following problem....

The problem does NOT occur if users copy the mdb file to their desktop
and run from there, but always occurs if two or more users run the file
from the network location.

You shouldn't be sharing MDBs: each user should have his/her own copy of the
database, preferably on his/her hard drive.

If you're concerned that they may not be using the most up-to-date copy of
the database, check the free AutoFE updater Tony Toews has at
http://www.granite.ab.ca/access/autofe.htm

 

[dbahooker]

MDB is for babies; lose the training wheels.

Access Data Projects are a MUCH superior product.

Spit on mdb people.

-Aaron
ADP Nationalist

 

[dbahooker]

ADP you don't have to be constantly updating the frontends.. the tables
and queries -- and functions for that matter-- always live in a single
place.

it is MUCH easier to manage; much simpler; and MUCH better performance.

and crap; development is easier!

-Aaron
ADP Nationalist

 

[Scott]

Thanks for the response, Doug. That's a good idea... and Tony Toews
certainly has done a great thing by sharing that app.

For anyone reading this in the future, I found the solution in the
following post... it made me realize that I was using DoCmd.Save
instead of RunCommand acCmdSaveRecord.
-------------------------------------------------

From: "Allen Browne" <[email protected]>
References: <[email protected]>
Subject: Re: Action Save cancelled
Date: Thu, 28 Aug 2003 13:25:28 +0800

DoCmd.Save does not save the record. It attempts to save the form (e.g.
its
Filter property).

If multiple users or processes have the form open, the attempt to save
can
fail. Because of the monolithic save in Access 2000 onwards, this is a
common problem.


If you intended to save the record, try this:


RunCommand acCmdSaveRecord
DoCmd.Close acForm, me.name, acSaveNo

 

[Tony Toews]

MDB is for babies; lose the training wheels.

Access Data Projects are a MUCH superior product.

FWIW the Auto FE Updater is equally applicable to ADPs as you don't want to share ADP
FEs either.

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

 

[Tony Toews]

ADP you don't have to be constantly updating the frontends.. the tables
and queries -- and functions for that matter-- always live in a single
place.

You'd never just update queries and functions by themselves. You'd also updating or
adding forms and reports. Thus your answer is irrelevant.

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

 

[dbahooker]

Tony;

you see-- I don't need to share frontends; I don't need ot update
them... since half of your update effort involves queries and tables--
in an Access Data Project these are all stored IN ONE PLACE-- like they
should be.

MDB is for babies.

Only with crappy old platforms like MDB do you need a dozen shortcuts /
workarounds to get _ANYTHING_ done.

-Aaron
ADP Nationalist