Allow only one User connection to shared MS Access Database

Posted on 2007-07-27
Medium Priority
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
  • 5
  • 3
  • 2
  • +1
LVL 52

Accepted Solution

Gustav Brock earned 180 total points
ID: 19581111
> 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

jjafferr earned 180 total points
ID: 19581353
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

wsh2 earned 140 total points
ID: 19582043
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 27

Expert Comment

ID: 19582168
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

ID: 19582202
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

ID: 19590829
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 52

Expert Comment

by:Gustav Brock
ID: 19590999
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

ID: 19591542
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

ID: 19591567
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 52

Expert Comment

by:Gustav Brock
ID: 20712557
Did you find a solution?


Author Closing Comment

ID: 31407559
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!

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

840 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