AccessDB, some can access, some can't

Posted on 2014-02-04
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

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 39

Assisted Solution

als315 earned 50 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
Do email signature updates give you a headache?

Constantly trying to correctly format email signatures? Spending all of your time at every user’s desk to make updates? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today!

LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 150 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 34

Accepted Solution

PatHartman earned 250 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now