Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-12-2017, 10:58 AM   #1
chrisl1471
Newly Registered User
 
Join Date: Dec 2017
Posts: 11
Thanks: 4
Thanked 2 Times in 2 Posts
chrisl1471 is on a distinguished road
Auto timeout and close function reasons

Hello all, I have been building databases for a few years now, started after a fellow employee that has written most of our companies DB left....Tag your it! Anywho.....I have always been told that when building a multi-user network (split) DB that it is wise to build in a auto timeout (Idle Time) feature. That when triggered saves and closes the database after say 5 minutes of idle....However, I cannot find anything that justifies that statement? Can anyone tell me the good/bad of doing or not using this Idle Time function?

Since someone may ask how to do this I use the following code taken from stackoverflow dot com

Code:
Private 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
Sub IdleTimeDetected(ExpiredMinutes)
Dim Msg As String
Msg = "No user activity detected in the last "
Msg = Msg & ExpiredMinutes & " minute(s)!, The database must be restarted."
MsgBox Msg, 48
DoCmd.Quit acQuitSaveAll
End Sub


Last edited by chrisl1471; 12-12-2017 at 11:19 AM.
chrisl1471 is offline   Reply With Quote
The Following User Says Thank You to chrisl1471 For This Useful Post:
plalonde (06-21-2018)
Old 12-12-2017, 11:24 AM   #2
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Auto timeout and close function reasons

Welcome to AWF!

Auto-logout may or may not be good, depending on the company business model and what the end users will be doing with the program. Most of the advice starts "If your users don't log out when they go home (leave the program up and themselves logged in) it can cause corruption or related issues.

That said I'd really rather use a "Logout time" or "Logout request" rather than an idle time. Of course only you know if your business model is such that all users will ALWAYS be doing something within 5 minutes, thus justifying this requirement.

The two ways I've used this type of system in the past are;

1) When user logs in, their "User" record says when they should be logged off by (based on their schedule). If that time + 15 minutes goes by and the program is still running, it posts a message (and notifies the supervisor) and gives them another 15 minutes to finish before shutting down. Mostly intended to catch when a user forgets to log off and doesn't restart their computer at end of shift.

2) "System" table that holds system configuration information can have a "Shut down" time entered. ALL copies of the FE check this file every minute. If a "Shut down" time has been entered the user is notified and the timer is reset to check every 5 seconds instead of every minute. When the time is up, the front end shuts down. MOSTLY used when something needs to be fixed quickly.

I hope this gives you a bit of perspective on where you need to look to see IF you need an "Idle time" log out and what a reasonable amount of time would be.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
chrisl1471 (12-12-2017)
Old 12-12-2017, 03:17 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,313
Thanks: 62
Thanked 1,152 Times in 1,052 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Auto timeout and close function reasons

Quote:
However, I cannot find anything that justifies that statement?
You seem to have a method available for "how" to do this. You want to know WHY you would do this. OK, I'll give it a shot. You need to shut down to protect your database from the following scenario.

User X logs in. Diddles with the database somehow or another. Has a form open and has a partial change pending. So the database has a lock file on the BE showing that user X is making a change. User X is looking up something he needs to finish the transaction.

But suddenly user X takes a phone call and has to leave the office for some obscure reason. Maybe Hell just froze over and he has the only key to the boiler. So the site protocols force the computer to become screen-locked by Windows after some amount of time. That means the change is still there but not visible behind the screen saver. Access is in the "waiting for input" state.

So user X doesn't get back before close of business. And that night is the night that Microsoft, in all of its wisdom, publishes new patches with a mandatory download and reboot. So that buffer that was locked REMAINS locked. Windows reboots, but because Access didn't follow through on releasing the lock, the BE file (on a machine that might not have rebooted yet) is now locked as well because the channel to the FE system was closed in a ragged way. And that means that the file system on the BE machine is now "waiting for Godot" and as we all know, he ain't coming.

Now, until you reboot the BE's host, that BE file is locked hard. And there is not one bloody thing you can do easily to fix it short of a reboot to reset all network sockets.

If you had a timer running on the form in question, you would have had the OnTimer routine do a form Undo followed by a form Close followed by an Application Quit. At some point or another, the file system lock on the BE file would be released and nobody would give a rat's patootie about whether user X's machine got rebooted or not.

Now, there is one other reason you might wish to do this. If your network is at all shaky, a long session has a greater probability of running into a "shake" than a short session. Therefore, you would wish to reduce the "window of exposure" to network hiccups for untended machines. And that is where an auto-logout would be helpful.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
chrisl1471 (12-13-2017)
Old 12-13-2017, 04:30 AM   #4
chrisl1471
Newly Registered User
 
Join Date: Dec 2017
Posts: 11
Thanks: 4
Thanked 2 Times in 2 Posts
chrisl1471 is on a distinguished road
Re: Auto timeout and close function reasons

Thanks, DOC man that is what I thought it might be, just couldn't put it in words.
chrisl1471 is offline   Reply With Quote
Old 06-21-2018, 05:34 AM   #5
plalonde
Newly Registered User
 
Join Date: Jun 2018
Posts: 1
Thanks: 2
Thanked 0 Times in 0 Posts
plalonde is on a distinguished road
Re: Auto timeout and close function reasons

This is a bit on an aside. Maybe a new thread even. I have been looking to resolve users that never close out of the database. You look to have a working answer. Can you share how to implemented your code?

Thx
plalonde is offline   Reply With Quote
Old 06-21-2018, 05:44 AM   #6
chrisl1471
Newly Registered User
 
Join Date: Dec 2017
Posts: 11
Thanks: 4
Thanked 2 Times in 2 Posts
chrisl1471 is on a distinguished road
Re: Auto timeout and close function reasons

Its pretty simple really, build a new form that is blank....Then go to the property sheet and add "event procedure" and add the above code for the "on timer" under the "event tab", the set your "timer interval" to 1000 ms is what I recommend so it checks for the expired time every second. Next I built a AUTOEXEC macro, that the first form it opens is this new form ( I always name it "frm_detectidletime")....and set it to be Hidden....so users never see it. You can then set the CONST Idleminutes to be whatever length you want it to be...I always use 5 minutes, but it can be any you want it to be.

Last edited by chrisl1471; 06-21-2018 at 05:51 AM.
chrisl1471 is offline   Reply With Quote
The Following User Says Thank You to chrisl1471 For This Useful Post:
plalonde (06-21-2018)
Old 06-21-2018, 06:35 AM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Auto timeout and close function reasons

@ Chrisl1471,

Personally I'd also look up your "Time out" from a "User" file. Some users should have rather short time outs. These would often include front line users who should normally NOT be off unlocking Nick's boiler. Then you'll have managers and others who will be expected to do other things while having the program up.

As an example of a "Long timeout" use; Managers are sitting around a table. Your program is up on the big screen. They have brought up the sales forecast "report" and are leaving it on the screen to discuss it.

They may argue about it for an hour or more without doing anything in the program. They would be rather upset if your program shut down during their meeting.

Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
chrisl1471 (06-21-2018)
Old 06-21-2018, 06:54 AM   #8
chrisl1471
Newly Registered User
 
Join Date: Dec 2017
Posts: 11
Thanks: 4
Thanked 2 Times in 2 Posts
chrisl1471 is on a distinguished road
Re: Auto timeout and close function reasons

Great idea, I do not have a use for that with my databases currently as they are not used by managers for things like you suggested....but the concept is solid, and will come in handy for those that do have that need/requirement.

chrisl1471 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] SSMS - Query Timeout - how to extend timeout & Remote Collation Rx_ SQL Server 0 05-20-2015 09:06 AM
Inactivity timeout function kit_sune Modules & VBA 1 08-23-2014 05:57 AM
Help with Auto Close VBA TBC Modules & VBA 2 03-09-2011 08:05 AM
Make a Function TimeOut after a certain interval tembenite Modules & VBA 5 06-29-2005 04:38 PM
auto close? kwtmd General 10 09-29-2004 01:45 PM




All times are GMT -8. The time now is 09:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World