Solved

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

Posted on 2008-10-20
10
533 Views
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.
Thanks
0
Comment
Question by:fmichail
  • 6
  • 4
10 Comments
 
LVL 11

Expert Comment

by:miqrogroove
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.
0
 

Author Comment

by:fmichail
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
 
0
 
LVL 11

Expert Comment

by:miqrogroove
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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:fmichail
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.
Regards
 
0
 
LVL 11

Expert Comment

by:miqrogroove
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).

http://msdn.microsoft.com/en-us/library/bb256546.asp

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

Expert Comment

by:miqrogroove
ID: 22826772
0
 

Author Comment

by:fmichail
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
 
0
 
LVL 11

Accepted Solution

by:
miqrogroove earned 500 total points
ID: 22826916
http://www.access-freak.com/tutorials.html#Tutorial02

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).  "
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22826968
0
 

Author Closing Comment

by:fmichail
ID: 31508144
Dear miqrogroove

Thanks.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use VBA to update a record in a table in MS Access? 12 36
Dynamically Reorder List Box 4 37
User Level Security 6 38
Create report using crosstab query 11 29
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

775 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