Implement a no activity shut down in Access
Inevitably, a shared database application will be left open by at least one user for what seems like forever. This makes maintenance and upgrades difficult since it is not possible to delete or repair the file while a user has it open. To overcome this you have a few choices:
- Track down the offending user and make them close the database
- Use administrative rights on the shared file to force close the connections
- Implement a timer that automatically quits access after a period of inactivity
Option 1 is less than ideal, particularly if your application is run on a terminal server and does not utilize user level access control. Option 2 only works if your application is on a shared server that you have full administrative control over, and even then it’s just plain rude.
That leaves us with number 3…
Form Timers
There are two form properties that can be utilized to make this work, the On Timer event, and the Timer Interval property. The timer interval specifies when the code in the On Timer event will run. This is entered in milliseconds, so for code to run every minute, set this property to 60,000. We will use these functions of Access forms to monitor how long the database has been inactive, and force a quit.
Monitoring Activity
In order to monitor activity we’ll use a small form that captures the last use time and compares it to the function Now() every minute. This form is very simple and contains only 2 controls.
The only code on this form is in the Form_Timer event
Private Sub Form_Timer()
‘Checks if time limit is up and closes database
On Error GoTo trap
Dim Limit
‘convert minutes to serial time
Limit = Me.TimeLimit / 60 / 24
If Now() > Me.LastAction + Limit Then
DoCmd.OpenForm “CloseWarning”
End If
Exit Sub
trap:
MsgBox Err.Description
End Sub
By setting the Timer Interval property to 60,000, this code will be fired every minute to compare the current time to the LastAction control value. The LastAction control is set to default value of Now() and must be updated by code scattered throughout your database forms. Use events in other forms in the database to update this control by putting the line Form_ActivityMonitor.LastAction = Now()in the code where users are most likely to be doing something. The TimeLimit control is set using the default value property.
Forcing the Shut Down
If the ActivityMonitor form reaches it’s time limit, it will open a form called “CloseWarning”. This form has a 1 second timer and a hidden text box named “X” that will be used to count down 10 seconds and quit Access completely.
This is set as a modal dialog box so it cannot be avoided and provides the user a chance to stop the exit process. There are 3 bits of code in this one to make it work
Private Sub Form_Load()
Me.X = 10
Me.TimeDisplay.Caption = Me.X & ” Seconds”
End Sub
This bit sets the time to 10 seconds. Simply change the code here to allow more time for the user to respond if desired.
Private Sub Form_Timer()
Me.X = Me.X – 1
Me.TimeDisplay.Caption = Me.X & ” Seconds”
If Me.X = 0 Then DoCmd.Quit acQuitSaveAll
End Sub
This bit provides the count down display and finally fires off the Quit command if X reaches 0.
Private SubResetTimer_Click()
[Form_ActivityMonitor].LastAction = Now()
DoCmd.Close
End Sub
This bit resets the last action control from the Activity Monitor form to allow the user to abort the quit and reset the clock.
Making it work
In order for this to work, you’ll need to open the ActivityMonitor form upon opening the database. This can be called from an AutoExec macro or during the load of your main form called by the startup properties. It’s also a good idea to open the form hidden, so the user doesn’t have the opportunity to close it or modify the timer in any way. In VB, the open command would look like this:
DoCmd.OpenForm “ActivityMonitor”, , , , , acHidden
Once it’s open, it will tick away waiting for the timer to reach it’s limit, then warn the user and quit automatically. This will ensure that nobody can leave the database open for days on end and you just might be able to upgrade your application or perform a little design and maintenance sometime.