Question Run Code on Database Exit

[melinda_b]

When users successfully sign into our database, the sign-on form is hidden in the background. This sign-on form contains a code that executes with the On Close Event. (This code logs a time in a table to capture the time the user logged out.) Previously, the Log Out button on the main form executed said code. However, since the database could be closed many ways (x button in the corner, from the task bar, etc), it seemed logical to have the code execute on from the Close event of the hidden sign-on form since this form would close when the database closes. Mostly it works. However, when people have had their machine shut down for various reasons and the database is forced closed, the code does not execute properly and logout times are not captured. My searches online of "MS Access Run Code on Database Close" or "Exit" indicate to do exactly what I have already done. Does anyone know of any other way to ensure my code will run when the database closes? I know there is an AutoExec for opening. Anything like this for closing?

 

[CJ_London]

I don't believe it is possible - code wouldn't run if the computer lost power for example.

Perhaps you can incorporate an update to the logout time in the code that does this

However, when people have had their machine shut down for various reasons and the database is forced closed

- or at least have the code enforce a managed shutdown in some way

 

[melinda_b]

Thank you for the reply. You are suggesting I adjust my code to handle a proper shutdown in the case of forced shutdown? That's a good thought. Thank you.

A sudden loss of power causing computer shutdown wouldn't give programs a chance to close properly. However, I was thinking about the case of a shutdown in which Windows closes all open programs. In this case, I thought the programs would exit normally. In the case of Access, the forms all close and the code should still execute with the On Close Event of the hidden form. (I am being told by my users that when they forgot to shut their database down and Windows shuts it down, the logout time does not register which means the code isn't executing.) If there is something different about the way the database closes in cases like this and the forms are not unloading and closing, then this does most certainly explain why the code is not executing. Which is why I wondered if there was an event or a procedure for the closing of the database, instead of the closing of a form.

 

[CJ_London]

It is not an area I have much expertise but it sounds like it doesn't do a 'clean' shutdown - there may be a setting in windows you can set to enforce this.

Another alternative, if it is a case of users forgetting to shutdown when they go home is to have a timer event which closes Access after a period of non use - don't have any code but google 'automatic logout' or similar.

But I would also ask the question - what is the benefit of knowing when a user logs out?

 

[melinda_b]

Again, I appreciate the ideas you suggest. Recently a user stay logged in overnight and I was unable to perform exclusive functions after hours because of this so closing the database with a timer event is a nice suggestion for some users.

The reason the database captures the user's logout time is because it also captures the user's login time since the database has a time card function built into it.

 

[JLCantara]

@Melinda: I understand your need to monitor app usage since I had to do it for a very large app. I suggest creation of an 'empty' logout record while creating the login one. Then, using the timer suggested, update it say every 15 min. including an auto shut down (with message - thing of overtime!) will solve the problem...

 

[CJ_London]

when analysing the data you may want to set a default of say 5pm if the logout time is null. If the user works to 6pm and forgets to logout - it might encourage them not to forget! Or set a default based on the users normal hours.

Alternatively, again on a timer, update the field every hour or 30 mins whatever with a final update when the user closes access - and if they forget, at least you have a more accurate time on which to base your calculations.

 

[The_Doc_Man]

Unfortunately, this is not a zero-sum game. At some point, events can occur that make it impossible to maintain a proper "shutdown" time.

It is not clear to me that all Windows shutdowns will kill your session unless your users are impatient. Normally, what happens on our systems is that the user has stuff open, including my application, and clicks the shut-down button/option that appears when you click the Windows Start button at lower left, in the task bar. The shutdown code reaches my application, which sometimes will not allow you to exit. That hangs the shutdown, which gives two options - return to main window and resolve the hang-up, or force the issue and shut down anyway. You know that sometimes the user clicks the "Force" button. At that point, your session closure log is a lost cause. You can't stop that "Force" button but you can educate your users that it is not a good thing to use.

Another "gotcha" is the network-went-to-Hell-in-a-handbasket scenario. If you have a back-end that holds your data including audit logs, you will be unable to update the tables and that will cause traps. A Windows trap cannot interrupt another Windows trap so either the process hangs, totally dead, or it triggers Access itself to step in to resolve what is essentially a deadlock. But in either of those cases, your "closure log" is no longer possible.

If you lose power, you lose your ability to update doodlum-squat. I cannot find a way to convey the power-down trap from Windows through Access to the application, though there is such a thing as an API for most of the battery-backup systems that can send warning notifications. At least for my version of Access (2013), I can't find a Form event for PowerDown or PowerChange. The Windows Power Management API seems to be able to set the state of the system for inactivity changes and for long sequences of no activity (thus causing a power-save or a screen saver to activate.)

So my cursory search for three nasty scenarios turned up nothing.

Net result of this discussion: Always capturing the logout time is not something you can guarantee fully. Capturing the fact that the user's next logon seems to have involve an open session (and updating the session) might be all that you can do.

 

[melinda_b]

@JLCantara - Really good idea - I already have the setup you suggest - a blank logout record created on login. The code finds the login record for the user on the date and inserts the logout time via SQL from the hidden sign-on form Close Event. So you suggest using the timer to update the logout time every 15 minutes? I like it.

Can you tell me what do you mean here, though:

@including an auto shut down (with message - thing of overtime!) will solve the problem...

 

[spikepl]

You just tell everybody that an autoshutdown, for technical reasons, has to log a log-out time of 4:30 PM

 

[JLCantara]

@Melinda: not the close event but the hidden form timer event. When it's done check for a conventional close time, say 5:12:12345 PM. At log in, user is inform of the closing time and once the timer detect that time a modal message with a timer warns the user: no acknowledge after say 5 min? close the whole shabang...

P.S. - second taught: you will need to keep an acknowledged message time and decide on a repeat delay...

 

[MarkK]

So you suggest using the timer to update the logout time every 15 minutes? I like it.

I don't like running timers. It's not that big a deal, but an alternative is to update the logout record on user activity, so if the user does something, update the logout record to Now() + 10 minutes.

 

[melinda_b]

@MarkK - I like the user activity suggestion. However, a lot of the users log in and minimize while they work on other things. Most of them are CAD drafters. So the database is minimized, clocking time while they work. They occasionally pop in to the database key in the job number they worked on. There might not be a lot of key strokes/activity on which to base a time stamp. Some key their work as they go and others wait until the end of the day to key it all in at once. Some wait until the end of the week. The time stamps compare against user hours entered. Helps them keep their time straight.

 

[MarkK]

CAD? Like Autodesk AutoCAD? The full version of that ships with VBA (or used to). You could very easily update your Access database from AutoCAD with almost no user interaction. So handle the SelectionChange (guess) event of the active Viewport and update the database.

 

[melinda_b]

@MarkK - Yes - Autodesk AutoCAD. I just had a discussion with one of our drafters. She said she mostly uses model space, not Viewport. So I am trying to wrap my head around how to insert a time based on the use of the active Viewport. I'm going to guess I could probably trigger a code from model space instead. But she also told me she often leaves the files open all night so she does not have to wait for the file to load in the morning. So if she never closes the file, how would I trigger an event to stamp a time for the end of her work day?

 

[MarkK]

Well, in the object browser in the VBA IDE in AutoCAD, check out the AutoCAD object model. Look for objects that raise events that you can handle that would indicate that the user is active, particularly check the AcadApplication and AcadDocument objects. Do these raise a Modified event? If so you can save a user activity record for each modification (and maybe only for certain types of object), or for every tenth modification, or whatever.

It's fine if a user leaves the file open, since you are only recording their activity, not their inactivity. One impact of this though is that there may be AutoCAD activity BEFORE a user logs into the database in the morning

But further to that, if you are tracking activity, the idea of a login may degrade in value. Just record activity, and deem the duration ('cause that's what you're ultimately looking for, right?) to be . . .

duration = latest activity - earliest ativity + 10 mins

. . . and you can record activity from various applications, AutoCAD, the Database, Excel (or any office product). Then you can even start to see who's using what tools for how long, and get an interesting picture of your workflow . . .
Cool thread.

 

[melinda_b]

I'll take a look. The analysis of activity is interesting. It is also nice to know of the potential to report from AutoCAD. I've done some automation with other VBA programs as well. At the end of the day, at least for now, I just wanted to see if there were other programming methods available to be more consistent with the capturing of the Log Out time. Thanks!!!!

 

[JLCantara]

@MarkK: a previous post made me think of those new vba statements:

if the user does something then
update the logout record to Now() + 10 minutes
else
goto Hell
end if