AccessDB, some can access, some can't

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?

Who is Participating?
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.
RichNHAuthor Commented:
I've been informed that all users accessing the system use Access 2000 to open the DB.
RichNHAuthor Commented:
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.
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.

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

RichNHAuthor Commented:
Thanks guys, All your comments led to the solution.
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.

All Courses

From novice to tech pro — start learning today.