Solved

Detect exclusive access to current database

Posted on 2000-02-17
3
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

740 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