Solved

Detect exclusive access to current database

Posted on 2000-02-17
3
368 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now