Often, in order to get the best performance from your application (or determine what’s going wrong with it), you have to know what your application is really doing. That’s the role of logging: providing a trail of what was going on with your application. Garry Robinson shows you how to keep track of some key information about your Access database.
Key questions in managing a database include “How many users do I have?” and “How many simultaneous users are there at peak times?” Would you, for instance, like to know who is using your database the most? Would you like to know who is logging out of your database correctly and who is just using turning off their computer? Would you like to have discussions about database performance armed with real facts about how many users you have and who uses the database at peak times of the day, rather than just hearsay and opinion? I use the logging tool described in this article to keep my clients’ databases running smoothly, as the tool lets me gather a lot of the facts about the users’ environment. If you’re not already creating your own logs, you need this tool.
In this article I’ll show you how you can log information about the users of your database. The sort of information that you’ll be able to save includes the Windows username, the computer name, the Access workgroup username, the time that the user logged in, and (more importantly) the time that the user logged out. This is key information for making decisions about configuring your application. Along the way, of course, you’ll also see how to extract this information for any other purposes that you may have. You’ll also see how to guarantee that your database is shut down every night so that you can perform regular maintenance.
The groundwork
In all the serious databases that I’ve built for my clients, I always make sure that I have a hidden form that logs the user details of anyone who opens 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 starts.
- A form is opened in hidden mode and stays open in hidden mode for the duration of the session.
- This hidden form logs user and Access details to a log table.
- When the front-end database is closed, the hidden form closes gracefully as part of the shutdown process. The time that the user logged off is saved to the same log table.
In the sample database, the logging form provides an additional function: At midnight the hidden form will shut down the database automatically. This ensures that if you come in early for database administration, or have some automatic process running at night, you won’t have to battle with users who have left the database open before going home to get their beauty sleep. Closing the database is also critical if you’re going to perform backups of your data, so the form should close your application about 20 minutes before official backups start.
To open the logging form in hidden mode, you can either set up a line in the Autoexec macro or add a line of code in your startup form like this:
DoCmd.OpenForm "fxUserLogs", acNormal, , , , acHidden
The logging form
Because the logging form will rarely be seen by the end user, the form is a very plain-looking affair. In Figure 1 you can see the form that’s included in the download sample. Generally, this form will have a message and a button to make the form invisible again but it won’t have a close button in the corner. In addition, the form has a hidden textbox (called txtSession in the download sample) that holds the key of the record where the user session details will be held.
Figure 1
Before showing you the code that’s fired when the form opens, I’ll walk you through the constants used by the logging form that tend to change between different databases. These constants are stored as module-level variables at the start of my code to make it easier to find and change these values without wading through the VBA code. As you can see from the comments in the code, the constants are used to control user logging and for closing down the database at a specific time of the day:
'User Logging Constants 'Purpose: Logging 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 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
This form logs to a table when the form is loaded and then completes the log when the form closes. The first part of the form’s Load event concentrates on collecting the information that is to be logged. In the following example, you’ll see that the software uses a number of different functions to extract the logging information for us. In these examples we’re gathering this information:
- The Windows username (function User_FX)
- The user computer name (ComputerName_FX)
- The time of login (Now)
- The version number that we’ve given to the Access front end (discussed later)
- The path of the database
- The version of Jet (JetVersion_FX)
- The version of Access (AccessVersion_FX)
- The Sandbox mode (SandboxMode_FX).
Private Sub Form_Load() 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 Me.visible = False Me.TimerInterval = MSGMINS * 1000 * 60# On Error GoTo Quick_Exit If LOGGINGREQUIRED Then UserNameStr = User_FX ComputerNameStr = ComputerName_FX LoginTime = Now versNum = Nz(DLookup("versionnum", "uSysDBVersion", _ "versionid=1")) strFrontEnd = CurrentDb.Name strJetVersion = JetVersion_FX strAccessVersion = AccessVersion_FX( _ strExeVersion, strExePath) intSandbox = SandboxMode_FX(strSandBox)
For your reference, Table 1 lists the versions of Access as we know them and the version returned by AccessVersion_FX (assuming that you have the latest version of each installed).
Table 1. Access versions vs. Access version numbers as of September 2005.
Access version | AccessVersion_FX |
Access 2002 | 10.0.6501 |
Access 2000 | 9.0.0.6620 |
Access 2003 | 11.0.6355.0 |
Once those details have been collected, the software needs to create a unique key for the user log and store that value in a hidden textbox on the form. After that, it’s a matter of appending a record to the UserLogs table in the database with all the details collected above. To add the data I like to use a SQL insert statement, but a DAO or ADO recordset append is just as good. This code inserts the bulk of the information that’s collected about the Access session:
Me!txtSession = UserNameStr & " " & LoginTime DoCmd.SetWarnings False 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 I noted before, keeping the index for the user log in a hidden field called txtSession is key to being able to update this record when the database is closed down. The following Close event, for instance, uses that data to update the record with information gathered when the user exits (since I always want this data recorded, I turn off all errors and warnings by using a Resume Next in my error handler):
Private Sub Form_Close() 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
This is where you’ll find out about the users who aren’t closing down the application in an orderly fashion. Any user who’s just pushing the Off button on their computer (or who has had their computer fail on them) will have logged the information listed at the start of this article but won’t have the information recorded in this event.
Getting the Windows username and other functions
It would be remiss of me not to give an example of how some of the user logging information is collected. For this, I have a function that I’ve employed for many a year that retrieves the Windows username. If you’re hunting for this code in the download database, you’ll need to open the FXL8_Startup module. If you look at this module, at the very top you’ll find two public declarations that are used to set up a reference to the Windows function (API) called GetUserName. If you search the Internet or any good book on the Win32 API, you’ll find many examples of this GetUserName function that may include refinements beyond this version:
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 function is that a single person using a laptop or a computer that connects to a Windows domain can log into a database using either the Windows username from the local computer or, occasionally and only when they use their domain to log in, the username from the domain. This situation seems to occur if the local computer uses a mapped network drive to open the database rather than logging into Windows. I couldn’t find any software that would recover the actual domain account that was used to map the network drive and, for those instances, I had to set up software that was smart enough to recognize both Windows accounts.
The Windows user account is only one of the many things that the download database shows you how to log. I suggest that you try out each of the functions in the Immediate window to see what they return for your database. On my computer, I tried this call to three of the functions:
? user_FX, computerName_FX, JetVersion_FX What I got back was this: 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. This data tends to be very stable for most users and is probably only needed occasionally for analyzing your database.
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 includes. In that code you’ll find the following line that establishes the frequency that the software checks to see if it’s time for the database to shut down. 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 look at the Form’s Timer event (as shown in the following example). The code in this event looks for a particular hour in the day and then, if the timer code has fired up during the period defined by the CLOSEMINS constant, displays a message and closes the database down:
Private Sub Form_Timer() 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 that will persist after the database has shut down. I use automation to display this message in Microsoft Word in an unsaved Word document. 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 generic Word.Application object at runtime rather than relying on a VBA project reference, your project won’t have to cope with any Word version issues:
Sub SendMessageToWord(strToDisplay As String) On Error Resume Next 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 piece of information that I always try to capture in my 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 placed the front-end database on the server with the back end. Lately, I’m increasingly moving to running front-end databases on our users’ C: drives. This improves performance and reduces the chances of database corruption at the costs of having to install the application on each user’s computer (and upgrade the version on each user’s computer when we issue a new version of the application).
Because of this change, the version information for the front-end database is critical. With this information, for instance, it’s difficult to determine if a user is accessing the data with an out-of-date version of the front-end application. Or, if you do determine than an old version is being used, it’s almost impossible to figure out who the culprit is. I capture the information for the log with this code:
versNum = Nz(DLookup("versionnum", "uSysDBVersion", _ "versionid=1")) 'The name and path to the Front-End database strFrontEnd = CurrentDb.Name
If you’re thinking that you’ve never seen the uSysDBVersion table before, you’re right. The uSysDBVersion table is one that I invented for storing the version release information for all our front-end databases. You can see the design of the table in Figure 2. As this is a table that actually should be in a database as a system object anyway, I gave it the “uSys” prefix so that it wouldn’t fit in with the other system-level tables. There’s 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, if you’re looking for this table in the download database, use the Tools | Options | View menu choice and, on the View tab, check the System objects checkbox in the Show area.
Figure 2
When I’m storing version information, I also like to store the build number and name using the traditional 3.75b type structure (VersionName) that you’ll be familiar with. I also store the date of the release. However, to simplify comparisons between versions of the same software, I also store a sequential number (for example, 75) that I increment each time I put out a new version of the application. I store this information in a row with the VersionNum field (the primary key) set to 1. 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
As the users are very unlikely to see the UserLogs table, I do need to make sure that the users can see the VersionName. There’s 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 Figure 3, you’ll see the version details at the top of the form. In the unlikely event that your users have a problem, you can ask them what version of your front end they’re running and, if they’re running the wrong version, you may well have an “instant solution”: Upgrade to the current version.
Figure 3
As I’ve reached the end of the article, it’s time for my traditional plug for my Workbench for Microsoft Access utility. The Workbench 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’s proven to be very useful in all of our 100+ MDB front ends.
Setting up Access examination software
When you’re looking at the download software, you’ll need to decide whether you want your users to open the download database, log their details using the hidden form, and then continue on into the application as usual, or whether, after logging the details to the back-end database, you want to close the database. In the download, I’ve set up the database for the second scenario because it’s 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 closing the database.
Another plug: If you’re interested in more detail on logging and monitoring users, my book on Access security and protection covers the topic in chapter 6 and includes some details on setting up a system table in chapter 3. Actually, with that information, you can probably just read the relevant pages while standing in the bookstore.