Link to home
Start Free TrialLog in
Avatar of IEHP1
IEHP1Flag for United States of America

asked on

Exclusive access of Microsoft Access Database

Hi, I have what seems to be a pretty silly question so here goes:

Awhile back, we faced a rather annoying issue that occurred whenever 1 person was inside of our Microsoft Access database and the database wouldn't allow anybody else access into the MS Access database.

I had believe I solved the problem by unchecking the check box for Record-level locking under File-Options-Client Settings-Open databases by using record-level locking (was checked by default and I unchecked it for the MS Access database). The problem went away and all were happy\unannoyed.

The annoying occurrence is yet again rearing its ugly head now recently.

Default open mode is set as Shared, Default record-locking is set as No Locks and like I said, Open databases by using record-level locking is unchecked.

Do you know how this annoying issue can be solved?

Please let me know if you know,
Thank you in advance
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Let's start with the basics. Does your setup meet these minimum qualifications:

You have a 'split' configuration ... Back End (BE) with just tables, and Front End (FE) linked to BE ?
If so, does each user have a separate copy of the FE on their workstation ?

Unless both of these are true, you need to start here.  All bets are off until these conditions are met.

mx
<<Awhile back, we faced a rather annoying issue that occurred whenever 1 person was inside of our Microsoft Access database and the database wouldn't allow anybody else access into the MS Access database.>>

  #1 thing, make sure that every user has full read, write, and delete privs for the directories where the MDB(s) (if it's split) reside.

 Regardless of settings, if JET cannot create a LDB file (which JET uses to place locks on the database) because of lack of privs, the database will be opened exclusive.

Jim.
Avatar of IEHP1

ASKER

<<Regardless of settings, if JET cannot create a LDB file (which JET uses to place locks on the database) because of lack of privs, the database will be opened exclusive.>>

I can see in the directory where the database resides, that a .ldb locking file is created every time the database is being used (Check)

<<Let's start with the basics. Does your setup meet these minimum qualifications:

You have a 'split' configuration ... Back End (BE) with just tables, and Front End (FE) linked to BE ?
If so, does each user have a separate copy of the FE on their workstation ?

Unless both of these are true, you need to start here.  All bets are off until these conditions are met.>>

Yes we have a Back End (BE) MySQL server that stores the SQL tables and the Front End (FE) is the Microsoft Access DB linked to the SQL tables.

No, there is the one database that resides on a network drive (so that any new queries, reports, etc. that are created can be run by anyone and modified by the data analyst (myself) and the COO (my boss)).

<<#1 thing, make sure that every user has full read, write, and delete privs for the directories where the MDB(s) (if it's split) reside.>>

Not sure what privs mean (do you mean permissions?). To answer, yes I believe so.

Thanks. Where do we go from here?
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<<I can see in the directory where the database resides, that a .ldb locking file is created every time the database is being used (Check)>>

 You need to verify that for each and every user.  When no one is using the DB, first user in causes a LDB file to be created.  If that user does not have sufficent permissions, then the LDB is not created and the DB is opened, but in exclusive mode regardless of settings.

 Lack of permissions is the #1 issue when a DB ends up exclusive.

Jim.
I would say "so that any new queries, reports, etc. that are created can be run by anyone " is a pretty good suspect ...

mx
I would say "so that any new queries, reports, etc. that are created can be run by anyone " is a pretty good suspect ...

+1 for this - anytime a user would create a new object, they will require the database to be opened exclusively. Once they do that, they'd have to save their objects and close down the database in order to remove that lock. I doubt they're doing that.

As mx said: give each use their own copy of the FE, just like you would with any other non-web (i.e. desktop) application. Access is no different than those other desktop programs, and even though you CAN run Access in the configuration you're using, that does not mean you SHOULD run it that way.
<No points wanted>

IEHP1,

Just so we are clear, ...a plain vanilla "Split" database:
Back End: Tables only, on a network location
Front End: Queries, Forms, Reports, and "Links" to the BE tables, ...on each users local computer)
(there are exceptions in some cases for temp tables, ...etc)

See some of the many popular links on the subject, like this:
http://www.techrepublic.com/blog/10things/10-reasons-to-split-an-access-database/1119

JeffCoachman