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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
"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 :)
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

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).  "

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fmichailAuthor Commented:
Dear miqrogroove

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.