Solved

Detect exclusive access to current database

Posted on 2000-02-17
3
377 Views
Last Modified: 2011-10-26
How can I detect whether the currentdb has been opened for exclusive or shared access?

I'd like to display a warning at startup if the database has been opened exclusively.

I'm not securing my database, I'd just like to know if the Exclusive checkbox was selected when the database was opened.
0
Comment
Question by:mrt1
  • 2
3 Comments
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2530746
I've tried several ways to check this out.  About the only way that you can find out, as best as I can tell, is to try to open the database with a valid but unaccessable name.  Create a special user name which is hidden from the users and only has open rights to a DB.

Then if you try in code to open the same database with this special name and the database is opened exclusively, then you will get an error, which can be trapped with the message.

There are several ways to make this problem less likely:

1) Under Tools > Options > Advanced set the Default Open Mode to Shared.
2) Force or train the user to open the database via a shortcut.  It will open in the default mode.

Jim
0
 
LVL 1

Accepted Solution

by:
jelliott720 earned 50 total points
ID: 2530953
Article Q117539 in the Microsoft Knowledgebase offers the following

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:



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


Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit



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



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!".


Open the database not exclusively, and then repeat step 4. Note that the message does not appear.

Hope this helps...
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2533431
Be careful using

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

because if there was an error, you will get the err # back.  Since an error number is not True (-1), it will be treated as False.  This doesn't mean the database was not opened exclusively.  If might be better to change the code like this:

      Function IsCurDBExclusive () As Boolean
      '   Purpose: Determine if the current database is open exclusively.
      '   Returns: True if open exclusively or an error occured.
      '            False if database is not open exclusively.

        Dim hFile As Integer
        hFile = FreeFile
        IsCurDBExclusive = True

        With CurrentDB()
          If Dir$(.name) <> "" Then
            On Error Resume Next
              Open .name For Binary Access Read Write Shared As hFile
              IsCurDBExclusive = Err
              Close hFile
              On Error GoTo 0
          Else
            MsgBox "Couldn't find " & .name & "."
          End If
       End With
     End Function

The answer will always come back as true or false and eliminate having to run another test for True/False/Err in the calling routine.

In code do this:

    If IsCurDBExclusive Then
        MsgBox "Close and open in shared mode"
    End If

Jim

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

827 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