Solved

AccessDB, some can access, some can't

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

Continuing...
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?

Thanks,
RichNH
0
Comment
Question by:RichNH
7 Comments
 
LVL 1

Author Comment

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

Author Comment

by:RichNH
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.
0
 
LVL 39

Assisted Solution

by:als315
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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.
0
 
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.

Jim.
0
 
LVL 34

Accepted Solution

by:
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:
  Read
  Create
  Delete
Update users:
  Read
  Write
  Create
  Delete

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
    Else
        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
            Else
                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.
0
 
LVL 1

Author Closing Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Some time ago I faced the need to use a uniform folder structure that spanned across numerous sites of an enterprise to be used as a common repository for the Software packages of the Configuration Manager 2007 infrastructure. Because the procedu…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now