Backing up Multi-User Access Databases

Using VBA to Check Whether Anyone is using the Database

To find out whether someone is using a database, you will need to test whether you can open that database in exclusive mode. To try the demonstration form, open the sample database for the version of Access that you are interested in and choose Chapter 5 in the Demonstration Database Interface form. The sample that I want to demonstrate is a form called frmIsDBopenDAO. Open this form in design mode because you may need to change the location of the Northwind database. The following code snippet demonstrates how you might use the IsDatabaseOpen function. If the (Northwind) database opens in exclusive mode, the function will return a True result.

' This form will test if it's possible to open a database exclusively.
Const MYDBPATH = "C:\Program Files\Microsoft Office\Office\Samples\northwind.mdb"
Dim myDbIsOpen As Boolean

myDbIsOpen = IsDatabaseOpen(MYDBPATH)
If myDbIsOpen Then
  MsgBox "Database is already open or an error occurred."
Else
  MsgBox "Database is not being used by anyone."
End If

The logic used in the IsDatabaseOpen function commences by opening a DAO workspace object. By using that workspace object, we then attempt to open a database reference in exclusive mode. If the exclusive reference fails, it returns an error. We can then check the error number to see why we couldn't open the database exclusively.

Function IsDatabaseOpen(strDbPath As String) As Boolean
' This function tests whether a database is open.

Const FILENOTFOUND = 3024
Const ALREADYOPEN = 3356
Const ALREADYOPENEXCL = 3045
Const DISKDOESNOTEXIST = 3043

Dim wsp As DAO.Workspace
Dim myDbs As DAO.Database

On Error GoTo IsDatabaseOpen_error
  ' Returns reference to default workspace.
  Set wsp = DBEngine.Workspaces(0)
  ' Attempts to open an exclusive reference to another database.
  Set myDbs = wsp.OpenDatabase(strDbPath, True)
  ' No one is using the database.
  IsDatabaseOpen = False
  Set myDbs = Nothing
  Set wsp = Nothing

IsDatabaseOpen_Exit:

  Exit Function

IsDatabaseOpen_error:
  ' Test for errors, which are probably caused by trying to open the
  ' database in exclusive mode.
  IsDatabaseOpen = True
  Select Case Err.Number

    Case FILENOTFOUND
      MsgBox Err.Description, vbInformation, "File Not Found"
    Case DISKDOESNOTEXIST
      MsgBox Err.Description & vbCrLf & vbCrLf & strDbPath, _
       vbInformation, "Disk does not exist"
    Case ALREADYOPEN
      ' Opened by one or more people. One name appears in message.
      MsgBox Err.Description, vbInformation, "File Already Open"
    Case ALREADYOPENEXCL  ' Already opened exclusively by someone.
      MsgBox Err.Description, vbInformation, "File Already Opened Exclusively"
    Case Else
      MsgBox "Error number " & Err.Number & " -> " & Err.Description
  End Select
  GoTo IsDatabaseOpen_Exit

End Function

When you try to use the IsDatabaseOpen function to open a database that someone is already using in shared mode, error number 3356 (signified by the constant ALREADYOPEN) returns an interesting error description. Unfortunately, because someone is using the database in shared mode, this description incorrectly says, "You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'MY COMPUTER.' Try again when the database is available." Alternatively, if you have opened the database in exclusive mode and then use the IsDatabaseOpen function, it will return a message that says, "The file is already in use" (and doesn't mention it being exclusive at all).

There is a ADO version of the IsDatabaseOpen function in the download database.

Note:

Checking Whether Anyone Has the Database Open

Using VBA to Check Whether Anyone is using the Database