Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    How to Close Database if Inactive

    I tried to VPN into server to change the back-end database (Access 2016). Saw that the .laccdb file was there, and couldn't delete it or rename the database, indicating somebody was in the application with a live connection to the back-end DB. Had to drive to client and close front-end database on one PC, which was left running.



    I would like to figure out how to code a solution so if the user is inactive for a long time (say an hour), it will kick them out by closing the front-end database, which will then release the connection to the back-end database.

    I've seen some solutions on the web, all of them using form Timer event on a hidden or otherwise always open form. I have such a form in my application, so far so good.

    Problem is most solutions require global variables and some code in every other form of the application. Usually in Form_Current event, which sets a global counter variable back to 0. Otherwise the counter variable keeps incrementing and can trigger a Quit if it gets too large. So this works if a user moves between records on a form.

    Is there any way to accomplish the same thing without having to modify every other form in my database?

    I found some code using Windows APIs such as GetLastInputInfo, which is supposed to indicate whether the user's process is idle or not, but can't get that to work. This type of method would be preferable to coding every form in the database. Any ideas how I can make this work?

    Thanks...

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure why you would need more than one hidden form whose timer event looks for the trigger? Some put a file in a folder and have the trigger form look for it every x minutes or so. I used to set a flag (checkbox in a table field). One 5 minute warning came up (custom form) and closed in x seconds. Same form at 1 minute mark, closed in x seconds. After that, timer form shut down db. If you want in to do maintenance, longest you'd have to wait is five minutes. I'm not a fan of shutting down after x minutes just because someone isn't using it unless that causes a big strain on resources. Even then, I think your hidden form could get Screen.Active form and/or Screen.Active control and if neither/either didn't change in n checks, shut down accordingly. Point of that method is you should not need to code a whole bunch of forms.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    Not sure why you would need more than one hidden form whose timer event looks for the trigger? Some put a file in a folder and have the trigger form look for it every x minutes or so. I used to set a flag (checkbox in a table field). One 5 minute warning came up (custom form) and closed in x seconds. Same form at 1 minute mark, closed in x seconds. After that, timer form shut down db. If you want in to do maintenance, longest you'd have to wait is five minutes. I'm not a fan of shutting down after x minutes just because someone isn't using it unless that causes a big strain on resources. Even then, I think your hidden form could get Screen.Active form and/or Screen.Active control and if neither/either didn't change in n checks, shut down accordingly. Point of that method is you should not need to code a whole bunch of forms.
    Thanks for the reply Micron. Maybe I mis-stated. One form only needs the timer code to check periodically for whatever indication that there has been activity. Whether that indication is a file or the name of the current form and control is unimportant. That's half the solution. The other half is: How to affect the indicator when there is activity in the application? Most of the articles I've seen require you to put code in EVERY form, perhaps in the form_current event, to modify whatever indicator the timer event is looking for. So if no activity after 30 minutes (or whatever time desired), the database can close. But any activity in the database would reset the indicator to zero, and the inactivity time would start over. The timer event code would look for the indicator and see how much time has elapsed. If too much, close down. If not too much, rinse and repeat.

    So my question is: Can this be done without modifying other forms' code? One solution said there was a Windows API which could check for user activity, but I couldn't get anywhere with that one.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The way I do this is to have a hidden form that runs on startup and runs on a timer event every 30 seconds.
    If no activity is detected within a set time, a close down procedure begins and the app closes after another 30 seconds unless the user becomes active.
    However, to detect activity you do need to determine whether anything has been clicked or the mouse has been moved. That means keeping a check on the current form and control and mouse position
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks, Orange. I've seen The Microsoft method.

    Pros: No code needed in every form to reset the inactivity indicator. Easy.

    Cons: What if the user is moving between many records (click Next, Next, Next...) just to inspect those records without mofifying them. If the Form_Current method causes a particular control to get focus, this could look like inactivity to the timer code routine.

    Don't know just how realistic that "Con" is. Probably unlikely that a user would stay in the same form and not change the active control. Maybe I'm over thinking this.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Didn't follow the link, but to clarify what I suggested. If the one hidden timer form checks the name of the Active control and/or control and 20 minutes later, both are the same active form/control then it stands to reason that nothing is going on. I'm not seeing a need for that in every form.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by isladogs View Post
    The way I do this is to have a hidden form that runs on startup and runs on a timer event every 30 seconds.
    If no activity is detected within a set time, a close down procedure begins and the app closes after another 30 seconds unless the user becomes active.
    However, to detect activity you do need to determine whether anything has been clicked or the mouse has been moved. That means keeping a check on the current form and control and mouse position
    Thanks Colin. I'm in agreement with the sensing method, but just questioning the validity of the indicator (same form, control active). Seems like this could cause a false negative if you're simply scanning records on the form, one after the other. This may be over-thinking on my part. Any possibility of this could be mitigated by popping up a message to the user, who could then click a button to reset things.

  9. #9
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    Didn't follow the link, but to clarify what I suggested. If the one hidden timer form checks the name of the Active control and/or control and 20 minutes later, both are the same active form/control then it stands to reason that nothing is going on. I'm not seeing a need for that in every form.
    Just in case I replied too soon, allow me to clarify. There is only need to have the timer-based monitoring in one form. What I was referring to was an example on the web of an alternate method, which modified a global variable in the Form_Current event of every form (except the hidden monitoring one). The timer-event code would check this global variable instead of looking at the active form and active control. As I mentioned earlier, you could have a case where the user is simply looking at records one-after-the-other by tapping the "Next" arrow at the bottom of the form. If the same control gets focus as each new record comes up, that could fool the timer code into thinking the user was inactive, right? Please advise if you think this is an unrealistic possibility. Thanks again...

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If all you want is to be able to kick user's out so you can do backend maintenance you don't need to check if they idle or not. I have a back-end table where I can toggle a boolean field on or off; the main switchboard has a timer event that monitors that and if gets turned of displays a timed message and shuts down
    Code:
    If DLookup("[Shutdown]", "[usysShutDown]") = True And DLookup("[Initiator]", "[usysShutDown]") <> fOSUserName() Then
        'initiate shut-down
        Me.ckNoPrompt = True
        'display message
        MBox "Admin message from " & DLookup("[InitiatedBy]", "[qryShutDownMessage]") & ": " & vbCrLf & DLookup("[MessageDisplayed]", "[qryShutDownMessage]") & ". ", vbCritical + vbExclamation, "Admin Message", , , , , 5
        On Error Resume Next
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.RunCommand acCmdSave
        DoCmd.Quit acQuitSaveAll
    End If
    Click image for larger version. 

Name:	Screenshot 2022-05-18 114128.jpg 
Views:	37 
Size:	95.3 KB 
ID:	47829 Click image for larger version. 

Name:	Screenshot 2022-05-18 114427.jpg 
Views:	36 
Size:	78.7 KB 
ID:	47830Click image for larger version. 

Name:	Screenshot 2022-05-18 114522.jpg 
Views:	36 
Size:	81.2 KB 
ID:	47831

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds plausible, but will this navigation go on in excess of the time you'd allot for the time-out? I'm thinking 30 minutes or so? I suppose the timer event could attempt to retrieve the current record number and just move on if it doesn't apply. If the form is unbound, you could trap/ignore that error.

    Screen.ActiveForm.CurrentRecord
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Gicu View Post
    If all you want is to be able to kick user's out so you can do backend maintenance you don't need to check if they idle or not.
    Thanks Vlad. No, this isn't just for backend maintenance. This customer only works mornings, so I can always remote in in the afternoons. Normally, all I need to do is put up a new version of the front-end database on their server (running the front-end app, it checks if there's a newer version on the server, and updates them if needed). Any program changes or enhancements are distributed this way. This time, I needed to do a slight table change to the backend database, and couldn't because it was locked. Hence the need to shutdown the front-end database if they've been on too long without activity.

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, I get that, but sometimes you need to do that back-end change "now". The setup I showed you allows you flag the back-end as "not available" for any new connections and shut down running front-ends by monitoring one field from one form (whichever acts like your main switchboard and is always open). So regardless if they are idle or not you have full control on the back-end availability.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The main part of the inactivity code is as follows:

    Code:
    Private Sub Form_Timer()
    
    On Error GoTo Err_Handler
       
    Me.TimerInterval = 30000
        
    CheckActivity:
    '-------------------------------------------------------------
    'This section detects if the current active control does not change for a pre-set time.
    'If after this time there is no change, form frmSystemMessages opens
    'and gives the user 30 seconds to cancel, otherwise it closes the program.
    
    
    'The Global variable intTimeDelay determines how much idle time to wait (in seconds)
    'before running the IdleTimeDetected subroutine.
    'It is set in the SchConstants table as record number 11
       
    Static PrevControlName As String
    Static PrevFormName As String
    Static ExpiredTime
    Static Timeout
    
    
    Dim ActiveFormName As String
    Dim ActiveControlName As String
    Dim ExpiredSeconds
    Dim Lockout As String
    
    
    On Error Resume Next
    
    
    'Check to see if any program activity
    If intTimeDelay > 0 Then
    '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 form and control 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 _
        ActiveFormName = "frmLogoutStatus" Or ActiveControlName <> PrevControlName Then
           PrevControlName = ActiveControlName
           PrevFormName = ActiveFormName
           ExpiredTime = 0  'reset
        Else
           '  . . .otherwise the user was idle during the time interval, so
           ExpiredTime = ExpiredTime + Me.TimerInterval  ' increment the total expired time.
        End If
        
        ' Does the total expired time exceed the intTimeDelay?
        ExpiredSeconds = (ExpiredTime / 1000)
       ' Debug.Print ExpiredSeconds
        If ExpiredSeconds >= CLng(intTimeDelay) Then '18/12/2007 4431w STP - Added clng() entering loop when intTimeDelay is larger than ExpiredSeconds
            '  . . .if so, then reset the expired time to zero . . .
            ExpiredTime = 0
            '  . . .and open the form frmSystemMessages if no activity
            If intTimeDelay > 60 Then
                strMsg = "No user activity has been detected in the last " & CLng(intTimeDelay / 60) & " minutes."
            Else
                strMsg = "No user activity has been detected in the last " & intTimeDelay & " seconds."
            End If
            
            InactivityFlag = True
            
            DoCmd.OpenForm "frmSystemMessages", , , , , acDialog, "ShutDown"
        End If
    End If
    '-------------------------------
    
    
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        'create error message & log
        strProc = "Form_Timer"
        PopulateErrorLog
        Resume Exit_Handler
        
    End Sub
    I use a different method for removing all users when I need to close the database for maintenance.
    First of all I email all users to inform them in advance where possible
    I then set a boolean field Kickout in a table tblKickout = True.
    Doing that, starts a 5 minute countdown process before all users are locked out.
    That close down routine is implemented using other code in the above Form_Timer event
    It also prevents new users logging in

    After maintenance the Kickout field is set False & all users are emailed to inform them that maintenance is complete
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes Colin, that's pretty much what I have (ShutDown vs KickOut). I have a public function that I call on the first line of the AutoExec macro:
    Code:
    Public Function vcCheckForShutDown()
    If DLookup("[Shutdown]", "[usysShutDown]") = True And DLookup("[Initiator]", "[usysShutDown]") <> fOSUserName() Then
        'initiate shut-down
        'display message
        MBox "Admin message from " & DLookup("[InitiatedBy]", "[qryShutDownMessage]") & ": " & vbCrLf & DLookup("[MessageDisplayed]", "[qryShutDownMessage]") & ". ", vbCritical + vbExclamation, "Admin Message", , , , , 5
        On Error Resume Next
        DoCmd.Quit acQuitSaveAll
    End If
    End Function
    And the Mbox is a timed pseudo msgbox (custom popup form with custom buttons and timer).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-28-2018, 11:42 AM
  2. Database Design to Maintain Inactive Employee Records
    By Tylin in forum Database Design
    Replies: 6
    Last Post: 03-03-2018, 01:11 PM
  3. Replies: 5
    Last Post: 09-01-2014, 12:11 PM
  4. Multi Users Access database gets Inactive
    By drunkenneo in forum Access
    Replies: 3
    Last Post: 11-14-2013, 03:12 AM
  5. should i close inactive forms
    By Mclaren in forum Forms
    Replies: 1
    Last Post: 07-07-2010, 02:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums