Making Database Shutdown Automatically if it's Inactive

5/25/01 12:03:56 PM

The information here was derived from Microsoft Knowledge Base Article Q128814 .

The procedures here will allow you to set the database to shut down if the user doesn't commit any activity for a specific amount of time. Here are the steps:

Create a new form, and name it DetectIdleTime.

Set the following properties to the form: TimerInterval: 1000 OnTimer:[Event Procedure]

Enter the following code for the OnTimer Event Procedure:

      Sub Form_Timer()
         ' IDLEMINUTES determines how much idle time to wait for before
         ' running the IdleTimeDetected subroutine.
         Const IDLEMINUTES = 5

         Static PrevControlName As String
         Static PrevFormName As String
         Static ExpiredTime

         Dim ActiveFormName As String
         Dim ActiveControlName As String
         Dim ExpiredMinutes

         On Error Resume Next

         ' Get the active form and control name.

         ActiveFormName = Screen.ActiveForm.Name
         If Err Then
            ActiveFormName = "No Active Form"
            Err = 0
         End If

         ActiveControlName = Screen.ActiveControl.Name
            If Err Then
            ActiveControlName = "No Active Control"
            Err = 0
         End If

         ' Record the current active names and reset ExpiredTime if:
         '    1. They have not been recorded yet (code is running
         '       for the first time).
         '    2. The previous names are different than the current ones
         '       (the user has done something different during the timer
         '        interval).
         If (PrevControlName = "") Or (PrevFormName = "") _
           Or (ActiveFormName <> PrevFormName) _
           Or (ActiveControlName <> PrevControlName) Then
            PrevControlName = ActiveControlName
            PrevFormName = ActiveFormName
            ExpiredTime = 0
         Else
            ' ...otherwise the user was idle during the time interval, so
            ' increment the total expired time.
            ExpiredTime = ExpiredTime + Me.TimerInterval
         End If

         ' Does the total expired time exceed the IDLEMINUTES?
         ExpiredMinutes = (ExpiredTime / 1000) / 60
         If ExpiredMinutes >= IDLEMINUTES Then
            ' ...if so, then reset the expired time to zero...
            ExpiredTime = 0
            ' ...and call the IdleTimeDetected subroutine.
            IdleTimeDetected ExpiredMinutes
         End If
      End Sub

Create a new module and name it Module1

       Sub IdleTimeDetected (ExpiredMinutes)
         Dim Msg As String
         Msg = "No user activity detected in the last "
         Msg = Msg & ExpiredMinutes & " minute(s)!"
         MsgBox Msg, 48
      End Sub

Insert the following code in the DetectIdleTime form right above the "on timer" code and right below the Option Compare Database Option Explicit code:

Sub IdleTimeDetected(ExpiredMinutes) Application.Quit acSaveYes End Sub

In the 1st BLUE SECTION with code, the line Const IDLEMINUTES = 5 tells the database to shut down after 5 minutes if it's inactive. If you wish to modify the length, simply modify the number in this line.

Special Issue--Making Timer Longer/Shorter For Certain Logged-In Users

7/18/01 2:03:20 PM

I wanted to make the timer have a longer duration for one of my users, and make the timer the normal, shorter duration for everyone else. The code to do this follows (it assigns a 45 minute time to user "fran", 5 minutes for anyone else):

Private Sub Form_Timer()
         ' IDLEMINUTES determines how much idle time to wait for before
         ' running the IdleTimeDetected subroutine.
         Dim IdleMinutes As Integer
         IdleMinutes = IIf(CurrentUser() = "fran", 45, 5)

That code would substitute for the 1st 5 lines of the previous code. (Only the last 2 lines are different.)