Auto Logout Users for DB Maintenance

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.

The Plan:

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

Once the form, frmLogoutStatus, is loaded, the countdown begins.  It is a simple date-time comparison with a static variable that is set when the form opens.  When the time reaches zero (or less than zero … remember the timer is not completely reliable) then the DoCmd.Quit method is called and the application closes. (I have removed error handlers to simplify the code.)

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.

Custom Timing

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.

» See All Articles by Columnist Danny J. Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles