Multi-user locking

[Joel Parker]

I have a split database consisting of a Data.mdb file and a Front.mdb
file, accessed with Access 2007. I am sharing this database with one other
coworker, so we've put Data.mdb on a central shared folder, and each of us
have a copy of Front.mdb (actually one of us is running a Front.mde from
the shared folder).

We're having some serious trouble with locking and being able to access
the forms, though. The database contains 5 major forms and maybe 20-30
subforms, all accessing another 20-30 tables with lots of 1:n
relationships.

The problem we're seeing is that it seems as if when one of us has just
one of the forms open, the other can't access any of the forms. When we
try to open one, we get a vague "Error opening form" type message. The
effects vary a bit depending on which forms are open, like for example
when one of us has Form1 open, the other can't access any of Form1-Form5.
But when Form2 is open, the other can access Form4.

I was under the impression that Access could be used in a multi-user
setting without much trouble, just locking the specific records that are
being accessed? This would be a huge hindrance on us if having one form
open on one computer basically locked the other computer out of the
database altogether.

Any thoughts on what might be going on here?

 

[Tony Toews [MVP]]

I have a split database consisting of a Data.mdb file and a Front.mdb
file, accessed with Access 2007. I am sharing this database with one other
coworker, so we've put Data.mdb on a central shared folder, and each of us
have a copy of Front.mdb (actually one of us is running a Front.mde from
the shared folder).

We're having some serious trouble with locking and being able to access
the forms, though. The database contains 5 major forms and maybe 20-30
subforms, all accessing another 20-30 tables with lots of 1:n
relationships.

The problem we're seeing is that it seems as if when one of us has just
one of the forms open, the other can't access any of the forms. When we
try to open one, we get a vague "Error opening form" type message. The
effects vary a bit depending on which forms are open, like for example
when one of us has Form1 open, the other can't access any of Form1-Form5.
But when Form2 is open, the other can access Form4.

I was under the impression that Access could be used in a multi-user
setting without much trouble, just locking the specific records that are
being accessed? This would be a huge hindrance on us if having one form
open on one computer basically locked the other computer out of the
database altogether.

This very much sounds like somehow you and your coworker are still
opening the same front end mde. To verify this both open up the
front end mde. You should now see three ldb files. One for each of
the front end mdes and one for the data mdb.

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Joseph Meehan]

I have a split database consisting of a Data.mdb file and a Front.mdb
file, accessed with Access 2007. I am sharing this database with one
other coworker, so we've put Data.mdb on a central shared folder, and
each of us have a copy of Front.mdb (actually one of us is running a
Front.mde from the shared folder).

I suggest that you each have that front end own on your own machine.
Doing what you are doing is defeating many of the advantages and it may
correct the problem you are having.

 

[Joel Parker]

This very much sounds like somehow you and your coworker are still
opening the same front end mde. To verify this both open up the
front end mde. You should now see three ldb files. One for each of
the front end mdes and one for the data mdb.

Hi, thanks for the reply.

I did some more extensive testing. As before, we've got a shared data file
"data.mdb" on a shared drive, but this time we each have a copy of
front-end "front.mdb" on our own computers.

I tested with two different sets of files:
"complex" - the original large database, 5 major forms, 20-30
subforms/accessory forms, 20-30 tables with interrelationships
"simple" - a simple database with a single table and a single form, with 3
records

To answer your question right off the bat, I made sure we're both using
our own copy of the front-end. I counted one *.ldb file on each computer
and one in the shared folder.

With the "complex" database, when my coworker opened front.mdb and opened
a form, I was locked out of all of the major forms with an error: "There
was an error executing the command."

With the "simple" database, when my coworker opened front.mdb and opened
the only form, I again was locked out of that form, but with a different
error: "Could not use 'S:\data.mdb'; file already in use."

I tried it as .accdb files as well, with the same result.

As for options, I have the following Advanced options set in my copy of
Access 2007 (my coworker is using the runtime):
* Default open mode: Shared
* Default record locking: No locks (tried as "edited record" too)
* Open database by using record-level locking: checked (tried unchecked
too)

Does anyone have any suggestions for me please?

Thanks,
Joel

 

[DAVID]

Any chance that it is a server problem? You need to have
all server service packs applied: there were some windows
server versions that had rare file locking problems.

 

[Joel Parker]

Hi all, thanks for the help.

I eventually narrowed it down to a tricky server problem: files were by
default created read-write only for the person who put the file there.
That meant when Access wrote its .ldb lock file to the server, nobody else
could read it or write to it.

We fixed that problem, and now things are working fine.

Thanks again,

Joel

 

[Tony Toews [MVP]]

I eventually narrowed it down to a tricky server problem: files were by
default created read-write only for the person who put the file there.
That meant when Access wrote its .ldb lock file to the server, nobody else
could read it or write to it.

We fixed that problem, and now things are working fine.

Thanks for posting back. I keep forgetting that this can be the cause
of your messages.

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

It sounds like I have the same problem. I know very little about access -
what did you actually do to fix the "tricky server problem"?

Our access worked great in Win 98 - but this year we needed new PC's. Cant
share very well at all. Slow to come up on 2nd PC. If 1st PC has db open,
2nd hangs when it tries to open.
Have default open mode as shared
Default record locking as no locks, tried edit record also
Have open db by using record level lock checked and not checked. tried both