How do you knock out users or Disconnect users from a database remotely?

[anoble1]

I have multiple Access 2003 databases. I do most of the editing in them all the time. I am having a problem of users all over the state being in the database, and won't get off of it. I currently use LDB Viewer. (Which just tells me the computer name that's currently connected). But, is there a way I can write something that will knock everyone out, of whomever I choose, so I can edit the database?

Thanks

[Megalog]

I keep a table in the backend database that stores user login/logout information. I also have a boolean field that only I can access. In the client frontend, there is a hidden form that checks that table every 5 minutes. If the boolean field is set to True, then it saves whatever record they are working, logs them out, and closes access.

So, at any time I can log in and check who's actively using the database, and issue warnings or plain kick them all out. It works great for when I'm doing a late night update and I discover that 3 people left their databases running when they left.

Also, you are using a Front End/Back End setup for your clients right? They're not all logged into the same file?

[NeoPa]

Not easy.

The locking system is designed to protect the integrity of the data within the database. You'd need to take that into consideration in any code you developed. I suspect something like this would involve a great deal of care taken to ensure the data was not compromised in any way in the process.

[missinglinq]

This site has a download with a working example and a number of other links concerning detecting who's currently using the database:

http://www.accessmvp.com/JConrad/accessjunkie/kickoff.html

Linq ;0)>

[anoble1]

I found a similar thread. And I have a question on it. Please see the question on the bottom.

Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim cn2 As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i, j As Long

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=S:\Workgroups\APC Power Delivery-Contract Services\Safety Meeting.mdb"

    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=S:\Workgroups\APC Power Delivery-Contract Services\Safety Meeting.mdb"

    ' 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 rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    'Output the list of all users in the current database.

    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", rs.Fields(2).Name, rs.Fields(3).Name

    While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1), _
        rs.Fields(2), rs.Fields(3)
        rs.MoveNext
    Wend

End Sub

Then you push CTRL + G, then type ShowUserRosterM ultipleUsers
and it list all the computer names connected. But, how do you get the LOGIN_NAME or the NTID of the person to work on there?

[NeoPa]

I could spend another ten minutes trying to make sense of the question, but I guess it's really down to you to spend the time to do that. I'll have another look if you do.

[twinnyfo]

Megalog,

I would be very interested in your code for viewing users and kicking people out of the database. I have frequent similar problems. It always seems that when I schedule an update over lunch (or evening), that this is the one time that everyone forgets to log out of the DB. i would love to have the ability to force a save (because I don't want to lose anything) and remove the user from teh system so I can perform updates.

Please post your code or contact me directly. I'd love to customize for my DB.

Many thanks!

[anoble1]

I assume you are using an Access database? If so, how many copies do you have of the database? I have 2. One on my local computer to where only I can access it. And I have one more on the network drive that everyone can see. Is yours like that?

[twinnyfo]

@anoble1,

Yes, on both counts: Access 2007, local master copy of database and second copy on a shared server. The time required for me to have users out of the DB is just long enough for me to copy over my new changes (several seconds, really), but is delayed when users have the DB locked for editing and run out to grab a byte to eat.

[NeoPa]

Moderator comment:
Technically this is a hijack, but bearing in mind ANoble1 seems to be engaging the interloper, and the question never got a satisfactory answer, I'm inclined to let it ride (Splitting it away into another thread that made any sense at all would be tricky anyway - to say the least).

If anyone manages to work out what's required and actually provide a solution from these extra posts then that's an added bonus.

[Megalog]

Twinny,
Sadly, I dont have an easy copy/paste code solution to provide for this. I posted my basic approach at the beginning of this thread, and you should use this as a roadmap to your custom approach. It will involve the use of a hidden form with a timer, a small table to store the login/logout information, and basic code to save & close access.
If you get stuck on any specific steps, leave a detailed post and I'll try to provide an answer.

[GrnMtn7]

Not sexy but it works

I have multiple Access 2003 databases. I do most of the editing in them all the time. I am having a problem of users all over the state being in the database, and won't get off of it. I currently use LDB Viewer. (Which just tells me the computer name that's currently connected). But, is there a way I can write something that will knock everyone out, of whomever I choose, so I can edit the database?

Thanks

I found a simple solution a year back or so that involves opening a form Let's call it zzMXLOCK with the autoexec macro. It opens hidden and the OnLoad Event looks like this:

Private Sub Form_Load()
Dim strFileName As String
strFileName = Dir("X:\NetworkFolder\Subfolder\LOCK.ozx")
        If strFileName <> "LOCK.ozx" Then
        Application.Quit acQuitSaveAll
        Else: DoCmd.OpenForm "zzMxShutdown", acNormal, "", "", acFormReadOnly, acHidden
        DoCmd.CLOSE acForm, "zzMxLock"
        DoCmd.OpenForm "MAINNAVIGATION PAGE”

This will only work for a split database where you can have the form “look” for a specific file
By renaming the file when the database goes to load and the macros are enabled (use .accde Or .mde)
The one form loads sees the file doesn’t exist and keeps the user from entering the DB. This locks the database, now the form “zzMXShutdown”m entioned above does that same thing but on Open and On Timer. To be nice I add a form called “zzAppShutdownW arn” that lets the user know they are about to be booted. The nice thing about calling a form and not a message box is you can more easily tailor the message later.

Private Sub Form_Open(Cancel As Integer)
    ' Set Count Down variable to false
    ' on the initial opening of the form.
    boolCountDown = False
End Sub

Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
    Dim strFileName As String
    strFileName = Dir("X:\Networkfolder\Subfolder\LOCK.ozx")
    If boolCountDown = False Then
        ' Do nothing unless the check file is missing.
        If strFileName <> "LOCK.ozx" Then
            ' The check file is not found so
            ' set the count down variable to true and
            ' number of minutes until this session
            ' of Access will be shut down.
            boolCountDown = True
            intCountDownMinutes = 2
        End If
    Else
        ' Count down variable is true so warn
        ' the user that the application will be shut down
        ' in X number of minutes.  The number of minutes
        ' will be 1 less than the initial value of the
        ' intCountDownMinutes variable because the form timer
        ' event is set to fire every 60 seconds
        intCountDownMinutes = intCountDownMinutes - 1
        DoCmd.OpenForm "zzAppShutDownWarn"
        Forms!zzAppShutDownWarn!txtWarning = "This application will be shut down in approximately " & intCountDownMinutes & " minute(s).  Please save all work. There will be no other warning"
        If intCountDownMinutes < 1 Then
            ' Shut down Access if the countdown is zero,
            ' saving all work by default.
            Application.Quit acQuitSaveAll
        End If
    End If

Exit_Form_Timer:
    Exit Sub

Err_Form_Timer:
    Resume Next
End Sub

[kdcrowley]

I also use a hidden form to track login info, which also stores a yes/no checkbox, which I can uncheck at anytime. Every 60 sec, the form checks for "loggedIn", and if 0 then opens a warning page. The warning page gives the user 45 sec notice to get out, and then does a DoCmd.Quit acQuitSaveAll. I've considered running code to change (temporarily, of course) that user's login info, so they can't just jump back in, but haven't needed it yet.

[ahd2008]

That's interesting discussion. Megalog, I am concerned to know what code you use to perform the kick off safely and save the records that are being used when you said " then it saves whatever record they are working, logs them out, and closes access"

Thanks

[Megalog]

After all the checks and measures are done, and the user logging is complete, I believe all it does is:

DoCmd.RunCommand acCmdSave
DoCmd.Quit