ACC: How to Determine If a Database Is Open Exclusively

ID: Q117539

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

When a database is open exclusively, no other process or program can gain access to the database file. When a database is open 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 if a database is open exclusively.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

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

The following sample function demonstrates how to use the Open statement to determine in what 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:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0)

   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 Database
        Dim hFile As Integer
        hFile = FreeFile

        Set db = dbengine.workspaces(0).databases(0)
        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 Debug window (or

   the Immediate window in version 2.0), and then press ENTER:

      If IsCurDBExclusive()=True Then Msgbox "It's Exclusive!"

   Note that you receive the message, "It's Exclusive!".

5. Open the database not exclusively, and then repeat step 4. Note that the

   message does not appear.

REFERENCES

For more information about the Open statement, search for "Open Statement" using the Microsoft Access 97 Help Index.

Additional query words:

Keywords          : kbusage GnlMu 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998