Here is a detailed article that shows you how to extract important information about your Access installation (and the version of Access that your application is running under). This is very relevant to users of the Workbench
 

Advanced Logging of Access User Details

Summary

In this article you will find out how you can log information about the users of your database. The sort of information that we will be saving is the Windows user name, the computer name, the Access workgroup username and the time that the users logged in and more importantly the time that the users logged out. This will help you run your database in a more regulated fashion and help make decisions using facts rather than hearsay. This article can go hand in hand with this article on the Examination of the Access setup of your users <<< Peter What name did you give the article <<<.

The Working Sequence of a User Logging Form

In all the serious databases that we have running with our clients, we always make sure that we have a hidden form that logs the user details of anyone who open the database. In just about all of these situations, the database that the user is supposed to open is a front-end database. So when a user opens a database the following sequence of events occurs.

- The front-end database will startup just like it always did

- A form will be opened in hidden mode and will (hopefully) stay open in hidden mode for the duration of the session.

- This hidden form will log some user and Access details to a log table

- When the front-end database is closed, the hidden form will close gracefully as part of the shutdown process and the time that the user logged off will be saved to the same log table.

In the sample that I have provided, at midnight the hidden form will shutdown the database automatically because heaven forbid, we wouldn’t want our users to miss out on their beauty sleep. A more technical reason for this feature is that it is good to know that if you come in early for database administration that you don’t have to battle with users that have the database locked and really are still getting their beauty sleep. Backups are also an issue, and the timing may be better suited to 20 minutes before official back ups start.

To open the logging form in hidden mode, you can either setup a line in the Autoexec macro or setup a line of code in your startup form as follows.

DoCmd.OpenForm "fxUserLogs", acNormal, , , , acHidden


Setting Up A Database Logging Form

An Access logging form is a very plain looking affair as it will rarely be seen by the end user. In Figure 1 you will see the design of the one form that is included in the download sample. Generally this form will have a message, a button to make the form invisible again and it will not have a close button in the corner. From a technical point of view, a hidden field (called txtSession in the download sample) is very important as it maintains the index key for the user session details.


Figure 1 – The User logging form in design mode.

Let us now digest the VBA code that is fired when the form opens and to do this, it is best that we first look at the constants that tend to change between different databases. These constants are stored above all the subroutines and functions to make it easier to identify these without wading through the VBA code. As you can ascertain from the comments in the code, we have constants for user logging and also for closing down the database at a certain time of the day.



'User Logging Constants
'Purpose - Logging of users as they open and close the database

'Define if you want to use the Userlogs table
Const LOGGINGREQUIRED = True
'User Administration Constants
'Purpose - Shutdown and user messages
Const TABLEUSERLOGS = "tblUserLogs"

'Automatic Shutdown to close down the database if people leave
'database open before they go home
Const AUTOSHUTDOWN = True
'Automatic shutdown hour (24 hour time - 0 = midnight)
Const AUTOSHUTDOWNHOUR = 0
' Minutes between checking for system
Const MSGMINS = 1
Const DATETIMEFMT = "dd-mmm-yyyy hh:mm"

'Module wide variables
Dim strSQL As String

As I mentioned in my introductory remarks, this form logs to a table when the form is loaded and then completes the log when the form closes. Let us have a look at the code that is fired up when the form loads and in particular the first part of the Load Event concentrates on collecting the information that is to be logged. In this example you will see that the software uses a number of different functions to extract the logging information for us. In these examples we are gathering details on the Windows Username (function User_FX), the user computer name (ComputerName_FX), the time of login (Now), the version number that we have given to the Access front-end (will be discussed in detail later), the path of the database, the version of Jet (JetVersion_FX) and Access (AccessVersion_FX) and finally the sandbox mode (SandboxMode_FX).

Private Sub Form_Load()

' Create and insert a login time entry into the login table
' Check to see if a system shutdown is imminent and provide warning messages

Dim UserNameStr As String, LoginTime As Date
Dim ComputerNameStr As String, versNum As Variant
Dim strFrontEnd As String, strJetVersion As String
Dim strAccessVersion As String
Dim strExeVersion As String, strExePath As String
Dim intSandbox As Integer, strSandBox As String

'Always hide this form as the user shouldn't know
'that it is there
Me.visible = False

Me.TimerInterval = MSGMINS * 1000 * 60#

'Whilst this form is important, it should not interfere
'with the user interface. Hence the quick exit
On Error GoTo Quick_Exit

If LOGGINGREQUIRED Then
  'Retrieve the user details, computer and time
  UserNameStr = User_FX
  ComputerNameStr = ComputerName_FX
  LoginTime = Now

  'The version of your Access solution
  versNum = Nz(DLookup("versionnum", "uSysDBVersion", _
   "versionid=1"))

  'The name and path to the Front-End database
  strFrontEnd = CurrentDb.Name

  'The version of jet
  strJetVersion = JetVersion_FX

  'Access version details and path to executable
  ' As of 22 June 2005
  ' Access 2002 latest version 10.0.6501
  ' Access 2000 latest version 9.0.0.6620
  ' Access 2003 latest version 11.0.6355.0
  strAccessVersion = AccessVersion_FX( _
  strExeVersion, strExePath)


  'Sandbox number from registry
  intSandbox = SandboxMode_FX(strSandBox)

Once those details have been collected, the software needs to create a unique key for the user log which is stored in a hidden text box (called txtSession) on the form. Then it is a matter of appending a record to the userlogs table with all the logging details collected above. For this I like to use an SQL insert statement but a DAO or ADO recordset append is just as good. This concludes the bulk of the information that is collected about the Access session.

  'Save the key field for use when the database is closed down
  Me!txtSession = UserNameStr & " " & LoginTime
  DoCmd.SetWarnings False

  'Now insert a record will all the logged values

  strSQL = "insert into " & TABLEUSERLOGS & " " & _
  "( SystemUsername, AccessUsername," & _
  " ComputerName, loginTime," & _
  " VersionNum, sessionId," & _
  " FrontEndPath, jetVersion," & _
  " AccessVersion, AccessExeVersion," & _
  " AccessExePath, SandBoxDescr ) values " & _
  "('" & UserNameStr & "','" & CurrentUser & _
  " ','" & ComputerNameStr & "'," & _
  " #" & Format(LoginTime, DATETIMEFMT) & "#," & _
  versNum & ",'" & Me!txtSession & "', '" & _
  strFrontEnd & "', '" & strJetVersion & "'," & _
  "'" & strAccessVersion & "','" & strExeVersion & _
  "', '" & strExePath & "','" & strSandBox & "')"

  DoCmd.RunSQL strSQL
End If


Quick_Exit:
  DoCmd.SetWarnings True

End Sub

As was mentioned before in the form load section, keeping the index for the user log in hidden field called txtSession is impartant to update the same record when the database is closed down. The following Close event of the form shows how that update is achieved.

Private Sub Form_Close()

  ' Update the current login with a logout time"
  ' We always want this to complete so we turn off error messages and warnings.
  ' This should only happen when the database is closing down normally.

On Error Resume Next
If LOGGINGREQUIRED Then
  strSQL = "UPDATE " & TABLEUSERLOGS & _
   " SET logOffTime = #" & _
  Format(Now(), DATETIMEFMT) & _
   "# WHERE (((sessionId)='" & Me!txtSession & "'));"

  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
End If

End Sub

Windows Username and Other Special Functions

It would be remiss of me not to give an example how some of the user logging information is collected and for this I will have a look at the function that I have employed for many a year that retrieves the Windows username. If you are hunting for this code in the download database, you will need to open the FXL8_Startup module. If you look at this module, at the very top you will find two public declarations that are used to set up a reference to the Windows function (API) called GetUserName. Anyway all you need to do to log the Windows user is to retrieve the username using the User_FX function. If you search the internet or any good book on the Win32 API, you will find many examples of this GetUserName function that may include refinements on the one listed below.

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function User_FX() As String

On Error Resume Next

Dim lSize As Long
Dim lpstrBuffer As String
Dim trimStr As String
lSize = 255
lpstrBuffer = Space$(lSize)
If GetUserName(lpstrBuffer, lSize) Then
  User_FX = Left$(lpstrBuffer, lSize - 1)
Else
  User_FX = "Unknown"
End If

End Function

One of the interesting and perplexing things about the GetUserName Windows user name function is that a single person using a laptop or a computer that connects to a Windows domain can occasionally log into a database using either the Windows username from the local computer or if they use the domain to log in then the username on the domain is recorded. This situation seems to occur if the local computer uses a mapped network drive to open the database rather that logging into Windows. I could not find any software that would recover the actual domain account that was used to map the network drive and for those instances we had to setup software that was smart enough to recognize both windows accounts.

Anyway the Windows user account is only one of the many things that the download database shows you how to log and I suggest that you try each of the Functions out by simply typing ? FunctionName into the Immediate window. Eg

? user_FX, computerName_FX, JetVersion_FX

returns
GarryR C300-FX 4.0.8618.0

If you wish, you can customize your own code so that some of the very detailed information such as Sandbox mode and Access and Jet versions is only collected once a week (say Wednesday) as it tends to be very stable for most users and is probably only needed for occasional diagnostic work.

Auto shutdown

When I was discussing the Load event code for the hidden logging form earlier, I glossed over the Auto shutdown code that this form also executes. In that code you will find the following line that establishes the frequency that the software checks to see if its time for the database to shutdown. For the download database, I set this time to be one minute but in a live database, once every five minutes will do the trick.

Me.TimerInterval = MSGMINS * 1000 * 60#

For the rest of the shutdown code, you need to find the Form Timer event (as shown in the following example). Here the code has a simple modus operand, the timer looks for a particular hour in the day and then if the timer code fires up during the period defined by the CLOSEMINS constant, the database will close down, closing down all open objects as it does so.

Private Sub Form_Timer()

' Close down the database at midnight (in case anyone has left the database open)
Dim myDate, myDownTime, myUpTime
Dim myMessage, minsDiff As Integer

Const CLOSEMINS as Integer = 20

On Error GoTo Quick_Exit

If AUTOSHUTDOWN Then
  If Hour(Time()) = AUTOSHUTDOWNHOUR And _
   Minute(Time()) <= CLOSEMINS Then

   lblMessage.Caption = "This Access database called" & _
    CurrentDb.Name & " is CLOSING NOW for Automatic Administration. " & _
"The time is " & Now()
Call SendMessageToWord(lblMessage.Caption)
Application.Quit acQuitSaveAll
End If
End If

Quick_Exit:

End Sub

As part of the closedown process, I like to include a crafty piece of code that gives the user a message about what has happened to their database. For this I like to use Microsoft Word Automation code that displays a message in an unsaved Word file. If you take nothing else from this article, feel free to use this subroutine to display messages to users. As the code binds to the Word Automation at run time rather than relying on a VBA project reference, you project will not have to cope with any Word version issues. Take it from me, that is a good thing.

Sub SendMessageToWord(strToDisplay As String)
On Error Resume Next

'Send a message to word - used primarily for shutdowns

Dim objWord As Variant

Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible.
.visible = True
' Open the document.
.Documents.Add
.Selection.Text = strToDisplay

End With
Set objWord = Nothing

End Sub


Version Logging and Display

Another of the logs that we always try to capture in our log table is the name of the software and the version number that the user is using to access the back-end database. For a long time I always ended up placing the front-end on the server with the back-end but lately we seem to be increasing moving to running front-end databases on our users C: drives. As a result, this sort of information is vital. Anyway just to refresh you memory, we capture this information for the log as follows.

'The version of your Access solution
versNum = Nz(DLookup("versionnum", "uSysDBVersion", _
"versionid=1"))

'The name and path to the Front-End database
strFrontEnd = CurrentDb.Name

At this point in time you may be wondering what the uSysDBVersion table is and why you have never seen it before. Actually it a name that we invented for storing the version release information for all our front-end databases and it comprises of a table with the following core fields (see figure 2). As this is a table that actually should be in a database as a system anyway, we allocate it the “uSys” prefix so that it would not stand out. There is also a security benefit in using this prefix because Access will hide any table in the database container that has a “uSys” or “mSys” prefix. So BE WARNED, if you are looking for this table in the download database, use the menu Tools ~ Options ~ View and choose the System objects check box. I digress, back to the design of the version table that you can see in Figure 2.



Figure 2 – The version table that we store in all our front-end database

When we are storing version information we like to store the build number and name using the traditional 3.75b type structure (VersionName) that you will be familiar with and the date of the release. We also like to store a sequential number (eg 75) which we find is much easier to deal with when making comparisons between versions of software. Finally we store this in a table that has a primary key (VersionNum) of “1” one. To make sure that we only ever have the one record, we generally will add a validation rule on the Primary key that says “=1”. That way the table can only ever have one record.

Displaying The Version Number To The User

As the users are very unlikely to see the userlogs table, we do need to make sure that the users can see the VersionName and there is no better place to display this information than in a very visible spot on the main form of the front-end database. To help you achieve this in your database, the download database has a form called fxShowDbVersion that demonstrates how to set this up in your database. If you look at this figure, you will see the version details at the top of the form. Now in the unlikely event that your users have a problem, you can ask them what version of your front-end they are running and if they are running the wrong version, you may well have a solution because they have somehow managed to start the wrong database or the latest version with that all important bug fix just hasn’t been installed.


Figure 3 – A form that will display version information in your front-end database

And as I have got to the end of the article, it is time to plug my popular Workbench for Microsoft Access program because this has a form that allows you to look at and change the version information in the usysDBVersion table without even opening the database. This didn’t seem such a big thing when we first started but it has proved to be very useful in all of our 100+ MDB front-ends.

Setting Up Access Examination Software

When you are looking at the download software, you will need to decide if you want your users to open the download database, log their details using the hidden form and then continue on into the database as usual or simply open the download database, log their details and then close the database. In this case I have setup the database for the second scenario because it is easier for you, the reader, to open a form in design view when you can see it. If you want to go to the normal hidden logging method, modify the autoexec macro to open the fxUserLogs form in hidden mode and change the code under the OK button so that it hides the form rather than closes the database.

User logging in Garry's book

Garry’s book on Access security and protection includes a lot of detail on logging and monitoring users in chapter 6 and some details on setting up a system table in chapter 3.

Conclusion

If you are still wondering why bother with all this logging code. I can tell you that the information that is enclosed in the logs are very-very useful. For one you can use the information to find out who is using the database the most, you can find out if certain people are logging off incorrectly (the dreaded ON/OFF button) and you can have a discussions with real facts about how many and who uses the database a certain times of the day. In reality we tend to use the logs most of the time just to help use keep the database running smoothly as we can ascertain a lot of the facts about the users environment. If you are not already creating your own logs, I commend this hidden logging form for your databases.
 

Release Versions Details

You will receive a demonstration database that shows you how to display release details for "your Access database". Read more

Other Access Workbench Topics
Home Page | What is an LDB File | Advanced Ways To Start The Workbench   | Orders

The Access Workbench Help File  - Internal Database Logging