Workbench Help File - Internal Database Logging Of Windows User IDs and Login Times

Internal Database Logging Of Windows User IDs and Login Times
Applies to Access 97, 2000, 2002

As it is not feasible on most networks to restrict the usage of a computer to a single person, the computer name and Access workgroup username provided by the Access Workbench, may not be enough information to track down an individual. The computer name also makes it difficult to find a user when they use a database on a variety of computers. To reduce this confusion, it is good practice to log the Windows user ID as they open the database. Other information that is useful to log is the Access workgroup username and the time the person opens and closes the database.

Notes: A Windows User Id is the name that is used to log on to a Windows 2000, Windows XP, NT or Windows 98 network or computer. An Access workgroup username is the name that you select when logging into Microsoft Access. If you do not select anything, you will generally be logging in using the Admin username.

The demonstration database includes a form (see Figure 1) that demonstrates what objects you need to log user activity in your database. This form also demonstrates how to log usage of individual Access forms and reports.

Figure 1. The demonstration database shows how to log users and object usage.

What you will need to import into your database to make the demonstration examples work.
- UserLogs table
- UserAdmin table
- UserObjectLogs table
- frmGR8_UserLogs form
- basGR8_Startup module
- qryGR8_UsersLoggedInNow query
- rptGR8_UserLogs report

Working example: Shutting down twenty users

A database that I’ve been working on for the last three years has about twenty-five different users every day who either make changes, or run reports and query information. When I have to sort out problems, or upgrade the software, the first task is always to ask all the users to log out of the database. After a bit of ‘polite shouting’ across the desks, we generally reduce the list down to about five remaining users. These people can usually be found at meetings or buying a coffee downstairs. To find out who these users are and where they are located, I look at the log of Windows user IDs in the database and filter the list down to the names of those who’ve not logged off during the day. This gives me a few Windows 2000 User IDs that I can give to the manager to find out where they sit. Then we try and contact those people and when all else fails we go to their computers and shut them down. In some instances, we will use the UserAdmins table to shut people down automatically.

Initiating a User Log
To generate details that log when the user opens and closes the database, I open an Access form called frmGR8_UserLogs (see Figure 2). This startup AutoExec macro opens the frmGR8_UserLogs as a hidden form. This is illustrated using Visual Basic as follows

DoCmd.OpenForm “frmGR8_UserLogs”, , , , , acHidden

Figure 2. Shows the simple interface of the user logging form that will be hidden from users.

The form load event in the form frmGR8_UserLogs collects information for the log using visual basic as follows.

Dim sqlStr As String
Const dateTimeFmt = "dd-mmm-yyyy hh:mm"

Private Sub Form_Load()

Dim UserNameStr As String, ComputerNameStr As String
dim LoginTime As Date, wkgUserStr As String

Me.visible = False

' Whilst this form is important, it should not interfere with the user interface
' That is why there is a quick exit

On Error GoTo Quick_Exit

UserNameStr = User_FX
ComputerNameStr = ComputerName_FX
LoginTime = Now
wkgUserStr = CurrentUser

The Visual Basic shown above gathers information using the functions provided in the demonstration database:
- User_FX which finds the Windows User ID using system API calls
- ComputerName _FX which finds the computer name using system API calls
- CurrentUser which returns the current workgroup username

Now that we have that vital information, we need to save it to the UserLogs table (shown in Figure 3). An SQL insert statement generates the log when the user opens the database (using code deliberately not shown).

Completing The User Log
Of importance is the key field in the UserLogs table which comprises of the Windows user Id and the time. The key field value is first stored in a hidden text field on the form. We then reuse that key field value using the form close event to update the time that the person logged out .

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.

... ( code deliberately not shown)

End Sub

You may be curious as to how we guarantee that the form is closed whenever the user shuts down Microsoft Access. Thankfully, Access will close all open forms when it shuts down and the close form event will be fired. If the database is shutdown by an unforeseen event such as a power failure, the open forms are not closed properly and the UserLogs table will not have an entry in the LogOffTime field (see Figure 3).

{Click on picture to view}
Figure 3. The Userlogs table stores the system name, access workgroup ID, computerName and the time that a person logged into and out of the database.

To view this information you can either go directly to the UserLogs table or you can view the information in a query or a report. The rptGR8_UserLogs report (see Figure 4) summarizes the number of times that a person has opened a database (logged in), when they last opened it and if they still have the database open. This is quite useful for administration purposes.

{Click on picture to view}
Figure 4. A report showing Window User ID Information

The final piece of administration that I like to handle with this frmGR8_UserLogs form is to close down the database at midnight using the form timer event (code not shown):

Hint: User logging works best if you manage your Access startup properties to ensure greater user compliance.