Allow only one User connection to shared MS Access Database

Posted on 2007-07-27
Last Modified: 2012-08-14
Is there anyway to configure an access database so that it can only be opened by only one person at a time?

I know of the options in tools->options->advanced and this does seem to successfully prevent editing of tables once they are opened by another user. However, it doesn't seem to stop changes being submitted from forms?

I really want to lock them out completely, as that way there is no ambiguity.

I have tried setting up a shortcut with the "\excl" switch on the end, but this still allows access by another user, if they open the database directly by double clicking on the file.

Can anyone offer any advice, or is this something which is just not possible without some coding effort?

The users are using a mix of MS Access 2000 and 2003, but I think it is originally an Access 2000 database.
Question by:TeamLink
    LVL 48

    Accepted Solution

    > I have tried setting up a shortcut with the "\excl" switch on the end,
    > but this still allows access by another user, if they open the database
    > directly by double clicking on the file.

    No, it is the other way around: If one user has opened the mdb exclusively, other users cannot open it; if one or more users have opened it shareable, another user cannot open it exclusively.

    That said, it is quite easy to prevent users from logging in.
    Any shared database, say shareddb.mdb, has a corresponding lock file, shareddb.ldb. New users are recorded in this lock file. Thus, if you prevent this, they cannot log in to the database.

    This can be achieved very easily. Right-click on the ldb file, choose Properties and - at the bottom of the Properties' box - mark the attribute Read-only. Click OK.
    Now the lock file is write-protected which doesn't affect the users already logged in, but other users will get the message, that the database file is in use and cannot be opened.
    You could create code that - when the mdb has opened - sets the file attribute of the ldb file to Read-Only, and - prior to closing - resets the file attribute to Read-Write. Study the on-line help for how to locate the ldb file of the mdb.

    LVL 27

    Assisted Solution

    you can do the following:

    We will create a Table, with a field called Locked,
    when a user opens the mdb, the opening code will look at the field Locked,
    if it is equal to 0 (No), then allow the user to log in, and mark this field as -1 (Yes).
    when a user logs in and Locked equals to -1, then the mdb quits.
    when the user who is using the mdb, who logged in, when s/he wants to quit, then Locked should be changed to 0 and the mdb closes.

    do you need help with code?

    LVL 14

    Assisted Solution

    In both cases above, if a Clinet abnormally terminates, your system may be left in a state requiring administrative action to fix. My solution is similar to jjaffer's very worthy one.. except for the fact that if the client drops everything will be fine again.

    As in jjaffer's very fine solution, add a table named "Locked". In the table add one record with a key value of whatever you want. Then place the following code into the startup routine.
    '  Add a reference to Microsoft ActiveX Data Objects 2.x
    '  Variables
       Dim adoCN As ADODB.Connection, strCN As String
       Dim adoRS As ADODB.Recordset, strRS As String
    '  Initialize
       Set adoCN = New ADODB.Connection
       strCN = _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source='" & CurrentDB.Name & "';"
       Set adoRS = New ADODB.Recordset
       strRS = _
          "SELECT [Key] FROM [Locked] " & _
          " WHERE [Key] = [MyLockRecordKey] "
    '  Lock Record
       On Error GoTo TAG800_ERROR
       adoRS.Open strRS, adoCN, adOpenStatic, adLockPessimistic
       GoTo TAG900_EXIT
    '  Cleanup Database Connection
      On Error Resume Next
       adoRS.Close: Set adoRS = Nothing
       adoCN.Close: Set adoCN = Nothing
       On Error GoTo 0
       MsgBox "Table Is Locked. Try Again Later."
    End Sub
    This procedure uses record locking to test for previous activity. If the Client goies down, the record lock should be released.
    LVL 27

    Expert Comment

    well done wsh2, and thanks for showing me the drawback of my process.

    after posting my ealier comment, something came to my mind,
    how about doing this:

    tools->options->advanced -> Defaul open mode -> Exclusive

    This SHOULD do it all, right?
    as it will open the mdb in an Exclusive mode, thus prevent others from logging in.

    LVL 27

    Expert Comment

    I read this from Access help

    Set options for a shared Access database (MDB)  
    Note:  The information in this topic applies only to a Microsoft Access database (.mdb).

    On the Tools menu, click Options.
    Click the Advanced tab.
    Do one or more of the following:
    Specify whether a Microsoft Access database opens in shared or exclusive mode by default

    Under Default open mode, do one of the following:
    If you want others to be able to open the Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) at the same time you have it open, click Shared.

    If you want sole access to the Access database when you have it open, click Exclusive.


    Even if you have set the default open mode to Shared, you can temporarily override this setting at the time you open an Access database.
    If you set up security in your multiuser environment, you can help protect from users opening an Access database exclusively by denying them Open Exclusive permission (permissions: A set of attributes that specifies what kind of access a user has to data or objects in a database.) for the database.  

    Author Comment

    Thanks for your comments so far.

    I don't really want to code anything. The reason - it's not one of "our" databases - it is simply a database our client has created. We look after their network and they seek our advice on the ability to lock the database, which is fine, but as soon as we start tinkering with it, and coding in it, it becomes our responsibility to support, and we don't want that!

    Secondly, I have tried changing the default settings as suggested in jjafferrs last comment, but it did not work. Even with the default set to exclusive mode, the database can still be opened by multiple users at any one time. It just can't be written to (I don't think). I thought this could have something to do with the database file being held on a Linux server, but, I've tested it on another DB in windows, and it seems to do the same.

    So, unless I am doing something wrong - and I am struggling to see how I can be, it seems the exclusive access mode either is not working, or does not do what microsoft seem to think it does!
    LVL 48

    Expert Comment

    by:Gustav Brock
    Rename the database file to, say, *.mdp. This will prevent users from double-clicking the file. You may also set the file attributes to Hidden, which will hide it for the average user.
    Then create a shortcut to open Access and launch the app with the /excl switch set, and distribute that to the users.

    LVL 27

    Expert Comment

    here is another way, use wsh2's solution as in:
    but like this:

    make another mdb, OpenExclusively.mdb,
    open a new module, put wsh2's code in a Function, call it OpenExclusively,
    call this Function from a Macro, call this Macro autoexec, place the following code there:
    runcode > OpenExclusively
    this will make the Macro run on startup of the mdb, and run the Fuction.

    So the code will check if this OpenExclusively.mdb is open exclusively,
    if it is, then the code will quit,
    if not opened exclusively, then it will open the real mdb.

    this solution allows you to use code, yet leave your original mdb untouched.

    LVL 27

    Expert Comment

    Ah, one last thing to complete this solution,
    use gustav's suggestion:
    to hide the original mdb, and the users can ONLY see OpenExclusively.mdb.

    so your original mdb should not be opened directly, but through OpenExclusively.mdb.

    LVL 48

    Expert Comment

    by:Gustav Brock
    Did you find a solution?


    Author Closing Comment

    thanks guys, I haven't actually implemented any of these solutions as we didn't really want to get involved with development work on the database (it just isn't worth it for many reasons).
    As such, I have tried to fairly split the points based on contributions to the question, even though I don't know for sure which was the best solution.
    I have tried to do this as fairly as possible, so please accept my apologies if you feel hard done by at all!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions 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 Ac…
    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…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now