Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


AccessDB, some can access, some can't

Posted on 2014-02-04
Medium Priority
Last Modified: 2014-02-08
I'm trying to figure out how to solve an Access problem for a company I do part time work for.  I can dial in and check the DB in question but I'm not there during the day when the users have this problem.  The network support guy emailed me the following comment:

We have an access file named log-ins2 located on our Windows 2K server. The problem people are having with it is that 2 people have full access to it and others have read only. If one of the full access people open it before the read only people do they get a 'can't lock the file' error.

The privs of the people to the folder have been checked and seem identical.  I looked in the DB and it's pretty much wide open, in fact some users enter data directly into tables.  Also, if a "readonly" user gets to open the DB first there doesn't seem to be any problems.

I'm thinking that perhaps the full access users are creating a log file with tighter privs on it?  But I don't know how that can happen.   There's not really any code running in the background that would do this that I'm aware of.

I did note that at least some of the people are still running Access 2000 and not Access 2007.  The support guy who contacted me said that when he opened the DB today on a Access 2K machine, he did not see the security messages that allowed the user to enable macros and such.   I do get those messages when I opened it on my PC but I have Access2007.  

The DB has an MDB extension and indeed when I open it up Access tells me it is in Access 2000 format.

Any ideas?

Question by:RichNH
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

Author Comment

ID: 39834425
I've been informed that all users accessing the system use Access 2000 to open the DB.

Author Comment

ID: 39834482
I asked the following questions:
The “read only” users don’t have a problem when the “full access” users aren’t on, correct?
Do any “read only” users get locked out when other “Read only” users are on?
How about if both “full access” users try to get on at the same time?

And got the following answers:
When more then one read only user opens the DB it works fine for all and any read all users that have it open.
When only read only users have the DB open none of them get locked out.
Full access users also do not get locked out if they have it open at the same time.
LVL 40

Assisted Solution

als315 earned 200 total points
ID: 39834933
Is your database splitted?
Can you check in options (I have no Access 2000 and can't show exact place) defaut open mode - it should not be set to exclusive
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39835324
How are you enforcing the "read only" users? Are you setting Form properties, or are you doing this at the file level (i.e. allowing on Read permissions on the database file)?

If you have users running 2007 or later, make sure they all have added the database location as a Trusted Location.
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 600 total points
ID: 39835427
When it comes to multi-user /read only access, there are four things:

1.  None of the DB's are marked read-only in the file system - right click and check properties.

2. All users have full read, write, and delete privs for the directory where the db resides.

3. Share permission (if used) is set for full control.

4. The default open option in Access is set for shared access, not exclusive.

That covers 90% of the problem usually.  The other issue that people need to understand is that from A2000 on up, design changes require exclusive Access.   By that I mean creating a new form or report, or modifying an existing one.  It does not apply to add, editing, or deleting data in tables.

This won't stop anyone from getting into the DB or make data read-only, but some people don't realize their making design changes and that it's different from data, then get confused over the real issues.

 The other issue for A2007 and up is the one Scott commented on, which is the macro security level and trusted locations.  That can add confusion as well.

 Take a look at the first four things I mentioned.   Once those are all checked/set, with all users out, delete any .LDB files in the directory, then try again.

LVL 39

Accepted Solution

PatHartman earned 1000 total points
ID: 39836314
In order for Access to support multiple users, the users MUST have create and delete permissions for the directory that contains the BE - the FE should be on the user's own workstation so he will have full permission there.  The first user to open a database will cause Access to create a lock file (.ldb or .laccdb) and the last user out will cause Access to delete the lock file.  The lock file is what Access uses to manage concurrency.

I have never tried creating a read only situation this way.  I usually use code within the app that controls what each user can do but give it a try.  It may not work because Access probably needs Write permission for the lock file

Read-only users:
Update users:

To do it with code, you have to create a login table with permissions.  Here's a code sample from one of my applications.  The first snippet goes in the form's Current event.  If the lock is permanent for an individual, you could put the code in the form's load event.  I prefer the Current event because that allows me to add an unlock button to unlock the code for updating on request.  The second piece of code is generic.  All you have to do is to set the Tag property to "NoLock" for any buttons or combos your form needs for functioning.  The AllowUpdates property locks everything so it is not useful if you want to allow your users to press buttons to open reports or use combos to find records.
    If Forms!frmLogin!chkOKUpdate = True Then
        Call LockControls(Me, False)        'unlock
        Call LockControls(Me, True)         'lock
    End If

Open in new window

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            If ctl.Tag = "NoLock" Then
                ctl.Locked = False
                ctl.Locked = bLock         'toggle locks
            End If
    End Select
Next ctl
Set ctl = Nothing

End Sub

Open in new window

PS - put the lockControls sub in a standard module so it will be available from all forms.

Author Closing Comment

ID: 39844443
Thanks guys, All your comments led to the solution.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

610 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