BOOTING OUT DATABASE USERS!!!! 1

[mabaulza]

We have a multi user database system, and every now and then we need to perform maintainance on the database.

It can only be done when no one is in the database. Is there any way of "booting out" these users by creating a button for admin users?

 

[KenReay]

Hi

Not as far as I know, but at various times there have been posts on automatically kicking users out if there have not pressed a key for (say) 5 minutes and of setting a flag to stop them logging in, try searching the Forums and FAQ

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[clapper62]

I have done this by creating a form that is always open and setting up a timer event on that form that checks a value in a table so that when you change that value to something specific the timer event does a quit command on the database.

 

[CautionMP]

To build on what Clapper62 contributed, we did this in an environment where we had 4 front end databases tied to 2 back end and 128 users and we found that the best solution was to build a small backend database that contained the ‘kicker’ signal and linked all the front end databases to it. This way we could keep users from logging into any of the databases while we were in the process of kicking everyone out to compact/repair the backend database.

 

[toddtharp]

Yes there is a way.

You do it by setting a flag in a lookup table, then in your onTimer event, check to see if the flag is thrown, if so, docmd.quit. You may need to have an additional field identifying the user, I don't, mine is global and once set it forces all open databases to close. I use a front end .mde for users and I set the flag from the backend.

I also put the check in the OnOpen event, so they cannot reopen the database until I've cleared the flag.

 

[slreynolds]

thread705-79802 covers this topic very well

 

[Wantabie]

Create a module and place this code into:

Option Compare Database

Sub ShowUserRosterAndPassiveShutdown()
    Dim cn As New ADODB.Connection
    Dim cn2 As New ADODB.Connection
    Dim cn3 As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim j As Long

    On Error GoTo ErrHandler
    
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=[red]<Backend path\MyBEDB.mdb>[/red]"
    
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=[red]<Backend path\MyBEDB.mdb>[/red]"
    
    ' Restrict other users from opening the database
    cn.Properties("Jet OLEDB:Connection Control") = 1
    
    ' Attempt to open another connection to the database
    cn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=[red]<Backend path\MyBEDB.mdb>[/red]"
    
    ' 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
    
    Do While Not rs.EOF
       Debug.Print rs.Fields(0), rs.Fields(1), _
       rs.Fields(2), rs.Fields(3)
       rs.MoveNext
    Loop
    
    ' Close one of the remaining connections
    cn2.Close
    
    ' Reopen the user roster to verify that no other users are in the
    ' database Output the list of all users in the current database.
    
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, , _
    "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    
    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", rs.Fields(2).Name, rs.Fields(3).Name
    
    Do While Not rs.EOF
       Debug.Print rs.Fields(0), rs.Fields(1), _
       rs.Fields(2), rs.Fields(3)
       rs.MoveNext
    Loop
    
    cn.Close
    
    Exit Sub

ErrHandler:

    For j = 0 To cn.Errors.Count - 1
       Debug.Print "Conn Err Num : "; cn.Errors(j).Number
       Debug.Print "Conn Err Desc: "; cn.Errors(j).Description
    Next j
    
    For j = 0 To cn2.Errors.Count - 1
       Debug.Print "Conn Err Num : "; cn2.Errors(j).Number
       Debug.Print "Conn Err Desc: "; cn2.Errors(j).Description
    Next j
    
    For j = 0 To cn3.Errors.Count - 1
       Debug.Print "Conn Err Num : "; cn3.Errors(j).Number
       Debug.Print "Conn Err Desc: "; cn3.Errors(j).Description
    Next j
    
    Resume Next

End Sub

Then create a blank form and place this code into it's module:

Option Compare Database
Option Explicit

Dim boolCountDown As Boolean
Dim intCountDownMinutes As Integer

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("[red]<your backend path>\[/red][green]<checkfile>[/green]")
    If boolCountDown = False Then
        ' Do nothing unless the check file is missing.
        If strFileName <> "[green]<checkfile>[/green]" Or strChecFile <> "[green]<checkfile>[/green]" 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
        'Close and Save the two main forms
        DoCmd.Close acForm, "frmTicketingSystem", acSaveYes
        DoCmd.Close acForm, "frmEditLocations", acSaveYes
        'Display warning message
        DoCmd.OpenForm "frmAppShutDownWarn"
        Forms!frmAppShutDownWarn!txtWarning = "Do to immediate maintenance. This application will shutdown in approximately " & intCountDownMinutes & " minute(s). All work will be saved on it's shutdown."
        If intCountDownMinutes < 1 Then
            ' Shut down Access if the countdown is zero,
            ' saving all work by default.
        DoCmd.Close acForm, "frmPutPicture", acSaveYes
        SetMDBdefaults
        Application.Quit acQuitSaveAll
        End If
    End If

Exit_Form_Timer:
    Exit Sub

Err_Form_Timer:
    Resume Next
End Sub

Now create a 0kb file and give it a uniquely identifiable extension (such as: .ozx) i.e. "chkfile.ozx", where
[green]<checkfile>[/green] = "chkfile.ozx"

Now all you have to do is create a macro that opens the form into the background. Call the macro "autoexec", to ensure it is the first thing that loads before anything else.

Be sure to set the form to run in the background, and that the check file is placed in the backend database directory. Once everyhting is in place and you have update all your clients "frontend" database. you can simple rename the check file to a different name and the app will pop up a message that the app will go down in # minutes and after the timer times out. it will automatically close the app on the clients workstation...

Keep in mind that this code will need to be placed on each individual "frontend" app.

 

[Wantabie]

Forgot to mention that you will probably need to modify some of the code. Such as:

        'Close and Save the two main forms
        [blue]DoCmd.Close acForm, "frmTicketingSystem", acSaveYes[/blue]
        [blue]DoCmd.Close acForm, "frmEditLocations", acSaveYes[/blue]
        'Display warning message
        [red]DoCmd.OpenForm "frmAppShutDownWarn"
        Forms!frmAppShutDownWarn!txtWarning = "Do to immediate maintenance. This application will shutdown in approximately " & intCountDownMinutes & " minute(s). All work will be saved on it's shutdown."[/red]
        If intCountDownMinutes < 1 Then
            ' Shut down Access if the countdown is zero,
            ' saving all work by default.
        DoCmd.Close acForm, "frmPutPicture", acSaveYes

Where you can just simply use a popup message to notify your users that the application will be going down. instead of having a form do the work, as i have done here. also the text in [blue]blue[/blue] can be disregarded, as those are the 2 important forms i have created.

 

[mabaulza]

Great Thanks alot for all your contributions! They were all very helpful.

 

[easyit]

Hi,

Similar to checking if a file exists, you can also check the value in a table in your backend. I think (not sure though) that this will a little more efficient on datatraffic.
i use a table with one record with two fields, exit and loggoff. If anyfield is set to true users cannot open the database (check on login). If logoff is being set users will get a popup telling them that the DB is closing down in a few minutes, and when this time is elapsed the exit value is set -> the DB closes.

You need a form to be open (always) for this to work and there is a problem when the screensaver kicks in. We have NT4 workstations and the screensaver sometimes disables msaccess, which results in the timers not being updated.

If you need code for this, just let me know.

Maarten