Solved

Detect exclusive access to current database

Posted on 2000-02-17
3
378 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

Technology Partners: 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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

756 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