Auto Logout Users for DB Maintenance
September 16, 2005
One thing Access developers love about using SQL Server as the back end is that it is easy to do maintenance. I can't tell you how many times I toured around an office, looking for users who had their client open and connected to the data so I could ask them to log out. Too many times, the offender was at lunch or away from their desks, with their desktops locked.
After suffering enough frustration, I searched for a solution to that problem and developed a few simple components that may be added to any Access database. All of the necessary code is included in the download for this article. Below, I will outline the process.
There is probably no gentle way to "kick" users out of an application, but when it has to be done, it has to be done. The idea behind this process is ...
1. Open and hide a form that periodically checks for the logout flag
2. When flag is true, open and show the countdown form
3. Continue scanning to verify flag was not reset to false
4. Quit application at end of countdown.
The countdown form I came up with is shown below. I like the yellow and red diagonal stripes because they cannot be mistaken for anything but the gravest of warnings. If the user has gone home, they will never actually see this dialog, but if emergency maintenance ever needs to be done during business hours, this image should provide sufficient warning and time for users to exit the application gracefully.
You may test the application of this process by running the Auto-Logout.mdb file that is included in the download. The demo form points to the table that contains a LogoutAllUsers field, which serves as the flag to trip the logout. You may experiment by toggling the switch on and off and see the warning appear and disappear, respectively. I keep this data in my "version" table, along with other pieces of data that have one distinct value, as this table is allowed to have only one row. It is either version 1.0.0 or it is not. Users must either be logged out, or they may not. As we will see, being assured of only a single such record simplifies the code somewhat.
Ever so little code required...
Your startup form should include a simple call to open the Auto-Logout form in hidden mode. That's going to look like this ...
DoCmd.OpenForm "frmLogoutTimer", , , , , acHidden
The form, frmLogoutTimer, contains the following code on its Timer event. Note that the default interval is shown below as 30,000, which amounts to 10 seconds. This value can be increased so that the timer is not firing so often. Remember that Interval of 3000 equates roughly to a second, so you would have to set the Interval property to 180,000 for a minute and 900,000 for 5 minutes.
After checking the flag, this code either increases the interval and opens the form, or closes the form and decreases the interval back to the default. That is really all there is to this piece. As a hidden form, no one ever really sees it, though in the image above I have made it visible for the purpose of the demo.
Private Sub Form_Timer() On Error GoTo Err_Handler Dim fLogout As Boolean fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]") If fLogout = True Then Me.TimerInterval = 180000 DoCmd.OpenForm "frmLogoutStatus" Else Me.TimerInterval = 30000 If IsLoaded("frmLogoutStatus") Then DoCmd.Close acForm, "frmLogoutStatus" End If End If Exit_Here: Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub
Private mdat_StartCountdownTime As Date Private Sub Form_Open(iCancel As Integer) mdat_StartCountdownTime = DateAdd("n", 3, Now()) Me!txtMinsSecs = " 3 minutes and 0 seconds " End Sub Private Sub Form_Timer() Dim intIMins As Integer Dim intISecs As Integer Dim fLogout As Boolean fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]") If fLogout = False Then DoCmd.Close acForm, Me.Name intIMins = DateDiff("s", Now(), mdat_StartCountdownTime) \ 60 intISecs = DateDiff("s", Now(), DateAdd("n", (intIMins * -1), mdat_StartCountdownTime)) If intIMins = 2 And intISecs = 0 Then Me.Visible = True End If If intIMins = 1 And intISecs = 0 Then Me.Visible = True End If If intIMins = 0 And intISecs = 20 Then Me.Visible = True End If If intIMins <= 0 And intISecs <= 0 Then DoCmd.Quit Else On Error Resume Next Me.txtMinsSecs = " " & intIMins & " minutes and " & intISecs & " seconds " End If End Sub
Notice that if the logout flag was turned off, that is, the admin no longer wants to log users out, then the form is closed and the Quit command is never reached. Note too that if the user hides the form, it is made visible every minute until the application is closed, to remind the user that time is running out. (That is why the interval is increased above from 10 seconds to 60 seconds when the flag is true.)
The last order of business is to update the form so users can see how much time they have left. Using the minute and second calculations, a message string is generated and applied to the control source of the display control. I did not do anything fancy here to truncate the "s" on minutes when the value is one. That would be graceful, but frankly, I never cared enough about the verbiage of this disposable dialog to change it.
Everything you need to create an auto logout routine is included in the download for this article except one thing: your personal touch. On some older machines, users can detect a slowdown when the timer goes off, so a 10-second interval might be irritating. You could up the value to 30 minutes, which would reduce user interference but require a longer wait to get at the back end for maintenance. In addition, the countdown code is tooled for 3 minutes but a few simple modifications could lengthen or shorten that time as well. Personally, I like the times as I have set them, but you will need to decide what works for your users, your network and your database admin.