Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Is the database opened exclusively?

    I have a db that I use as a datawarehouse. Every day, I dump all the data from the tables and reload it with current data from our legacy system. This gives users great access to customer and product data that they would otherwise have to hand key.

    The users link those tables into their own databases, or sometimes run queries directly in the datawarehouse and export the results.

    The problem I'm having is that once in a while a user will open the db with Open Exclusive.
    Not only does this lock everyone out of the database, it locks up all databases linked to it.

    My solution (because I don't want to go through the hassle of using security for it), was to
    have a hidden form open on startup with code like this in the OnOpen event (granted, the code's not pretty yet, but it should be functional):

    Code:
    Private Sub Form_Open(Cancel As Integer)
      If IsDBOpenedExclusive(CurrentDb.Name) = True Then
        msgbox "Please do not open this db in exclusive mode"
        application.quit
      End If
    End Sub
    
    Function IsExclusive(strDbName As String) As Boolean
      'try to open the db again to see if we raise an error
      On Error Resume Next
      DBEngine.opendatabase (stDbName)
    
      ' If the database failed with error 3045,
      ' it was opened exclusively by someone else
      ' or possibly by ourselves.
      IsDBOpenedExclusive = (Err.Number = 3045)
    
    End Function
    Unfortunately, if I open the database exclusively, the above code fires, but doesn't throw an error.

    Any idea what could be wrong? Is there another method I should use?
    Inspiration Through Fermentation

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I did not try (in a hurry at the moment), but I would try to reopen the database in another workspace:
    Code:
    Set wrk = DBEngine.CreateWorkspace("", "admin", "")
    set dbs = wrk.OpenDatabase (stDbName)
    Have a nice day!

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Thanks for the reply, sinndho. Still no luck, though
    Inspiration Through Fermentation

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I was going to recommend trapping the error and then evaluating the error number when I noticed you are not returning the boolean value. Your function is IsExclusive but you are setting IsDBOpenedExclusive to a boolean value. Option Explicit strikes again.

  5. #5
    Join Date
    Nov 2011
    Posts
    57
    Hi,

    I know this is an old post, but this is kind of exactly what I'm looking for. I also don't want to use security to avoid this problem.

    Could you tell me what you mean by having a hidden form? Does the code run every time the database is open? And were you able to get it to work correctly?

    I appreciate the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •