Tracking users entering and exiting Microsoft Access Front End

[ Access | Main ]   (Updated 2010-09-27)

I created some logic behind a hidden form which was opened when the database was opened. It added a record to a logging table containing relevant info such as network login name, work station name, entry date and time, Access version and appropriate msjet40.dll version (or other Jet dll version as appropriate). The autonumber ID of this record just created was placed in a unbound control on the hidden form. When the database was closed, which meant this form was closed, the form then updated that record, using the stored autonumber ID as having normally terminated.

To be more precise I created a userid table, and workstation name table and then the detail log table.  This allows me to easily inquire into the database.   Also I place the backup location on the user table as the network location for backup might be user specific in a distributed environment.  (I have one client on a Terminal Server system where the workstations are scattered throughout the province.   This is a stand alone system and not part of any network thus back up gets a bit tricky.)

I also placed on that table room for comments.  On which I place such things as date and time of backup. I have an automated backup routine which renames the backend and copies it to the backup location specified on the user table.

We could then monitor users who were having problems or were using abnormal methods of closing Access. It was rather funny to visit a user and state "I see you were having a problem yesterday. What was happening?" They'd look a bit surprised and then tell you.

One big problem at a clients site a number of years ago was Access 97 exiting abnormally when attempting to print.  This was a large network client and this meant we had to fix up their printer mappings a bit.  Once fixed they were just fine.

At that client we never had to worry about someone doing Ctrl+Alt+Del to cancel the app or hitting the power switch as they were good users.  Of course they might have figured out that we were monitoring.  <smile>  I suspect though it was more because I worked in an office or cubicle just down the floor from them.  I wasn't some remote IT jerk dictating how things worked.  The users would deal directly with me on minor issues.

I get my information which I log from the workstation name and the NT logon user name. See the Get Login Name API and Get computer name pages at the Access Web.

This comes in quite useful in tracking corruptions. The station which terminated abnormally leaves their record in this table not flagged as closing. Everyone else can exit normally so their record is flagged as having exited normally. Of course when you have a power failure everyone's record in the database gets left "open".

Note that this will not track users who enter the backend directly.  In that situation I have a form which opens on entry and which states that users shouldn't be here.  The only option on the form is to hit the Stop sign close button which exits them from the database.  I also disable viewing the database window by unchecking at Tools >> Startup >> Display Database Window.

[ Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available