Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Detect if have exclusive access to database (2000)

    When you click a button to run a macro, is there a way to detect if you are the only one using the database before the macro executes? Some way to stop the macro if you aren't the only one in the database. Display a message saying that the macro cannot be run at this time.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    Loraine,
    What does your macro do?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    In general it deletes from a table, runs a query to refresh data and appends data to the table
    Echo on = NO
    Hourglass on = Yes
    SetWarnings = No
    RunSQL = Delete from table;
    OpenQuery (runs an append query)

    I do this three times to refresh some tables that are behind some forms
    I have a button to run the macro but I don't want the macro to run if someone else is in the database. I can tell if someone is in a database but these are users that I want them to be able to refresh the tables but only if everyone else is out of the database. There will only be three other users.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Detect if have exclusive access to database (2000)

    Put the following function in a standard module:

    Public Function UserCount() As Long
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim n As Long

    Set cnn = CurrentProject.Connection

    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4 OLE DB provider. You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets

    Set rst = cnn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    Do While Not rst.EOF
    n = n + 1
    rst.MoveNext
    Loop

    'Output the count of all users in the current database.
    UserCount = n

    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Function

    (it's based on code from MSKB article How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2000)

    You can use it like this in your own code:

    If UserCount = 1 Then
    ' your code goes here
    ...
    End If

    or

    If UserCount > 1 Then Exit Sub
    ' your code goes here
    ...

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    I am sorry for being so dense. When you say put this in a standard module - do I click on module and click on new and enter the code there?

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Detect if have exclusive access to database (2000)

    That is correct Linda. That will create a standard module!
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    Yes, you should open a new or an exiting module.
    If you choose to use a new module, don't give it the name UserCount but anything else. A module and a function cannot have the same name.
    Francois

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Detect if have exclusive access to database (2000)

    Quite some code. Tx Hans. Is this code generalized. IOW... will it work on any Access DB. I ask as it could be useful in a general way to trigger code at an exclusive session!
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Detect if have exclusive access to database (2000)

    Rudi,

    This code will work in Access 2000 and higher, not in Access 97 as far as I know.
    You must have a reference to the Microsoft ActiveX Data Objects n.n Library. The default version for Access 2000 format databases (even in Access 2002 or 2003) is 2.1, I believe, and 2.8 or something like that for Access 2002/2003 format databases.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    How do I use this in conjunction with my macro. I see an Action for RunCode that runs a Visual Basic Function procedure. It asks for Function Name. Would I put UserCount() or just UserCount or UserCount() As Long? There is a place to put a condition. I would think the condition would be If UserCount > 1 but I don't know the right syntax . I want to stop all Macros if the UserCount >1 and give the person a message that says, "Macro cannot be run at this time. Try again when everyone is out of the database."

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    If you run your macro from a button, you could use the following code in the On Click event of the button :
    If UserCount > 1 Then
    MsgBox "Not allowed to run the macro now. Try again when you are the only user of the database"
    Exit Sub
    Else
    DoCmd.RunMacro "YourMacroName"
    End If
    Francois

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Detect if have exclusive access to database (2000)

    Macros are limited and inflexible. You should use VBA code. It may seem daunting in the beginning, but it's much more powerful, easier to debug etc.

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Detect if have exclusive access to database (2000)

    Thanx Hans. This code is now in my archive of valuable code!
    Cheers

    PS: How many times have I <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> ' ed you today! I hope you don't get light-headed quickly with all the beers I'm passing your way today! If you can't cope with the brew...I'll send you a bar fridge!!! (Just let me know!)

    Shall I................................. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    I would like to use VBA but I wouldn't know how to begin. The macro seemed the safest way for me to go. If I were to use VBA - The start would be If UserCount >1 then Exit Sub (I would like that message about cannot refresh tables at this time etc). I guess I could put the button on the data entry form since these records will not be updated until the code is run. I would use the on click event of the command button on my data entry form. Put - If UserCount > 1 Then Exit Sub. (The message)

    My macro does the following: Turns Echo off
    SetWarnings = no
    Howurglass on
    RunSQL - Delete from tblsearchclmgr, tblconsult, tblReplast, tblBroker, tblcboBroker
    OpenQuery - clmgrSearchAppend, Consult, qryforcboclmgrAppend, qrySearchBrokerAppend, qryforcboBrokerAppend

    I need a little (a lot) of syntax help.
    Would the procedure run faster using VBA code?

    Thanks for any help you can provide.

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detect if have exclusive access to database (2000)

    Open the form in design view.
    Select the command button.
    Open the properties window and select the Event tab.
    In the On Click textbox, click the drop down arrow and select [Event Procedure]
    Click on the button with the 3 dots.
    You are now in the VBA editor and the cursor is between the lines :
    Private Sub YourButtonNaam_Click()

    End Sub
    Between those two line enter the code from my previous post.
    Francois

Page 1 of 3 123 LastLast

Posting Permissions

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