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

Posted on 2008-10-20
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


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 500 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 your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

627 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