ACC2000: How to Determine If a Database Is Open Exclusively

When a database is open exclusively, no other process or program can gain access to the database file. When a database is open but not exclusively, other processes can gain shared access to the .mdb file. This article shows you how to use the Open statement in code to determine whether a database is open exclusively.

The Shared argument of the Open statement lets you try to gain access to a file in shared mode. If that file is the current database, and if it is open exclusively, you receive a "Permission denied" error message. No error message is received if the database is open not exclusively, or shared. You can trap for this error to determine in which mode the database is open.

The sample function presented here demonstrates how to use the Open statement to determine in which mode the current database is open. This function is most useful to programmers who want to warn users that the database being opened is in the incorrect mode.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Start Microsoft Access and open the sample database Northwind.mdb exclusively.
2. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

3. Type the following procedure:

   Function IsCurDBExclusive () As Integer
     'Purpose: Determine if the current database is open exclusively.
     'Returns: 0 if database is not open exclusively.
     '         -1 if database is open exclusively.
     '         Err if any error condition is detected.
   
     Dim db As DAO.Database
     Dim hFile As Integer
     hFile = FreeFile
   
     Set db = CurrentDb
     If Dir$(db.name) <> "" Then
       On Error Resume Next
         Open db.name For Binary Access Read Write Shared As hFile
           Select Case Err
             Case 0
               IsCurDBExclusive = False
             Case 70
               IsCurDBExclusive = True
             Case Else
               IsCurDBExclusive = Err
           End Select
         Close hFile
       On Error GoTo 0
     Else
       MsgBox "Couldn't find " & db.name & "."
     End If
   End Function
   					

4. To test this function, type the following line in the Immediate window (CTRL+G):

   If IsCurDBExclusive()=True Then MsgBox "It's Exclusive!" Else MsgBox "Not Exclusive!!"
   					

5. Press ENTER.
   
   Note that you receive the message, "It's Exclusive!"
6. Open the database not exclusively, and then repeat steps 4 and 5. Note that you receive the message "Not Exclusive!!"