Go Premium for a chance to win a PS4. Enter to Win


The access database is locked on the server when anpther user accesses it

Posted on 2008-10-20
Medium Priority
Last Modified: 2011-04-14
I have an application using MSAccess on a server and linked access database on each user c:\ drive linked to the common database on the server.

for some users the application is like deadlocked when another user access the same form selected by the first user (only some users not all of them). and the lock is released when the first one closes the form.

The application is working perfectly in tens of my customers sites, and the database is not opened exlusively by the code.

and even on that customer site some of the users do not have this problem. What areas should I look at?
any approach of testing is appreciated.
Question by:fmichail
  • 6
  • 4
LVL 11

Expert Comment

ID: 22764727
My first suspicion is that the application is in its default table-level locking mode.  This means if anyone is actively editing a bound control, nobody can access the entire table.  You will want to use row-level locking for this multi-user environment.

Author Comment

ID: 22782110
Dear miqrogroove
What you say makes a lot of sence, but I will describe the configuration in more details
User 1 has local AppWorker.MDB on his C. It is linked to a common MDB (db.mdb) on server
User 2 Has exactly the same copy of the AppWorker.MDB on his C, linked to the same common mdb (db.MDB) on the same server
when user 1 opens (some forms) accessing his AppWorker.MDB user 2 can open some forms, but others may expose him to a deadlocked.

when user 2 opens (ANY forms) accessing his AppWorker.MDB user 1 is never deadlocked. By the way, this is regardless of who opens the form or application first
Could you clarify which database db.MDB or user1 Appworker.mdb or user2 Appworker.mdb needs row locking?, and how to define row locking? is'nt enough to open the recordset as dynamic with optimistic locking?
Thanks in advance
LVL 11

Expert Comment

ID: 22782291
The Access database locking mode is actually specified in the Options dialog, which is not selectable unless an mdb file is open.

I would go to both of the computers, open the front-end mdb file, then click on the Tools menu, select Options, and click on the Advanced tab.  There are three important options in here:

"Default open mode" needs to be set to Shared in a multiuser environment.

"Default record locking" should be set to Edited record.  This will prevent any user from making changes while another user is actively editing (dirty record).  This has no affect on reading the database.

"Open databases using record-level locking" needs to be checked.  This is essentially the same option as above, except that it effects Recordset objects defined in VBA code as opposed to forms and datasheets.

I think you will find these options were not set correctly by default.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 22826587
Dear miqrogroove
Please accept my apologies for being late in reply. Your explanation looks convincing, I have opened the database yesterday on the client site, but he is using Office 2007 (I could not find how to check the locking), so please provide the following and take the points:
1- Can I programatically open the database with a preset record locking ...etc? and how?
2- How can I do the same in Access 2007?
Thanks in advance.
LVL 11

Expert Comment

ID: 22826712
"The following tables list the names of all options that can be set or returned from code and the tabs on which they can be found in the Access Options dialog box

"Advanced Tab

"Default open mode      Default Open Mode for Databases
Default record locking      Default Record Locking
Open databases by using record-level locking      Use Row Level Locking"

This article also gives some information about changing those options programmatically in Access 2007 (likely not backward compatible).


If you still can't find the options, we'll get someone with Office 2007 in here to make a screen shot :)
LVL 11

Expert Comment

ID: 22826772

Author Comment

ID: 22826802

Dear miqrogroove
I have to go to my client to check Access 2007 (I have here only Access 2003), so please send me a screen shot (if it does not cause any inconvenience to you), as I do want to close that question.
Concerning the programatic approach The link is not working, and what I need is to Programatically control opening the database from my VB6 application not from Access itself.
Please help. Thanks
LVL 11

Accepted Solution

miqrogroove earned 2000 total points
ID: 22826916

Scroll down to Picture 2.21

"Other important commands like the startup settings or main options of the 2003 tools menu have been moved to the Access Options dialog, which can be accessed through the Office button  pull down next to the Quick Access toolbar (see Picture 2.1 or Picture 2.5).  "

Author Closing Comment

ID: 31508144
Dear miqrogroove


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question