Checking Whether Anyone Has the Database Open

The first and simplest way to determine whether someone's in the database is to check for an .LDB file with the same name as the database that you are using. You can check in Windows Explorer as follows:

1. Open Windows Explorer and navigate to the folder that your database is in.

2. Make sure that the display format of the folder is View Details or View List.

3. Sort the files in the display by file name.

4. Find the database and look for a file with the same name and the .LDB file extension.

The .LDB file is a good indicator of other people using the database, but sometimes a user turns off a computer or Windows crashes, and the .LDB file remains open. To cover for these contingencies, you can manually check whether you have exclusive access to a database by doing the following:

1. Open Access.

2. Choose File Ø Open and navigate to the folder where your database is.

3. Select the file, click the Open button's drop-down arrow, and choose Open Exclusive (shown in Figure 5-1).

   
   Figure 5-1: Testing whether you have exclusive access to a database.

If your database opens without any problem, then you can copy it to your backup media or compact it. If you are copying the file, you will need to close Access before doing the backup.

Unfortunately, these manual processes are a little tedious, and you may want to automate the process a bit more. To help with that, I have developed two equivalent functions that will tell you whether a database is being used.

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

Note: