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

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
fmichailAsked:
Who is Participating?
 
miqrogrooveConnect With a Mentor Commented:
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
 
miqrogrooveCommented:
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
 
fmichailAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
miqrogrooveCommented:
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
 
fmichailAuthor Commented:
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
 
miqrogrooveCommented:
"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
 
miqrogrooveCommented:
0
 
fmichailAuthor Commented:

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
 
fmichailAuthor Commented:
Dear miqrogroove

Thanks.
0
All Courses

From novice to tech pro — start learning today.