Logging Windows Users, Computers, and Access Accounts

Because it is not feasible on most networks to restrict the use of a computer to a single person, the computer name provided by the ADO frmJetUserRoster , LDBView, and the Access Workbench (already mentioned in this chapter), may not provide enough information to track down an individual. The computer name also makes it difficult to find a user when that user uses a database on a variety of computers. To reduce this confusion, it is good practice to log the Windows user ID each time the user opens the database. Other information that I find useful to log is the Access workgroup user name and the time the person opens and closes the database.

Note  

A Windows user ID is the name that is used to log on to a Windows 2000, Windows XP, Windows NT, or Windows 98 network or computer.

An Access workgroup user name is the name that you select when logging on to Access. Access workgroup user names are explained in more detail in the chapter on workgroup security (Chapter 8).

The demonstration database for this chapter includes a form called frmUserObjectLogs (shown in Figure 6-7), which 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 6-7: The frmUserObjectLogs form shows how to log users and object use.

Here's a list of what you will need to import into your database to make the demonstration examples work.

• UserLogs table

• UserObjectLogs table

• frmMakeUserLogs form

• basGR8_Startup module

• qryGR8_UsersLoggedInNow query

• rptGR8_UserLogs report

User Story  

A database that I've been working on for the last three years has about 25 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 off the database. After a bit of "polite shouting" across the desks, we generally reduce the list 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 to the names of those who've not logged off during the day. I then have a few Windows 2000 user IDs that I can give to the manager to find out where those people sit. Then we try to contact those people and, when all else fails, we go to their computers and shut them down.

Initiating a User Log

To generate details that log when the user opens and closes the database, I open an Access form called frmMakeUserLogs (shown in Figure 6-8). I have designed this form deliberately to look like a system message. A startup AutoExec macro opens the frmMakeUserLogs as a hidden form, which the following VBA code snippet illustrates:

 DoCmd.OpenForm "frmMakeUserLogs", , , , , acHidden 

Figure 6-8: The hidden user logging form.

The form's load event in the form frmMakeUserLogs collects information for the log by using the following VBA code:

 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 ' Though 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 VBA code shown gathers information by using the following functions:

• User_FX , which finds the Windows user ID by using the system that the application programming interface (API) calls.

• ComputerName _FX , which finds the computer name by using the system that API calls.

• CurrentUser , which returns the current workgroup user name.

Now that we have that vital information, we need to save it to the UserLogs table (shown in Figure 6-9).

Figure 6-9: The UserLogs table, which stores logging details at time of entry and exit.

A SQL insert statement generates the log when the user opens the database, as follows :

 Me!txtSession = UserNameStr & " " & LoginTime DoCmd.SetWarnings False sqlStr = "insert into UserLogs " & _ "(SystemUsername, AccessUsername, ComputerName, loginTime, sessionID) " & _ " values ('" & UserNameStr & "','" & wkgUserStr & "','" & ComputerNameStr & _ "',#" & Format(LoginTime, dateTimeFmt) & "#,'" & Me!txtSession & "')" DoCmd.RunSQL sqlStr Quick_Exit: DoCmd.SetWarnings True End Sub 

Completing the User Log

Of importance is the key field in the UserLogs table, which includes the Windows user ID and the time. The key field value is first stored in a hidden text field on the form and added to the UserLogs table (demonstrated by the previous insert SQL statement). You then reuse that key field value by using the form close event to update the time that the person logged off into the table by using an update SQL statement, as follows:

 Private Sub Form_Close() ' Update the current logon with a logoff time. ' You always want this process to finish, ' so turn off error messages and warnings. ' It should happen only when the database is closing down normally. sqlStr = "UPDATE UserLogs SET UserLogs.logOffTime = #" & _  Format(Now(), dateTimeFmt) & "# WHERE " & _ " (((UserLogs.sessionID)='" & Me!txtSession & "'));" On Error Resume Next DoCmd.SetWarnings False DoCmd.RunSQL sqlStr DoCmd.SetWarnings True On Error GoTo 0 End Sub 

You may be curious to know how the form closes when the user shuts down Access. Thankfully, Access closes all open forms when it shuts down, and the close form event will be fired . If the database is shut down 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 column (shown in Figure 6-9).

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

Figure 6-10: A report that shows Windows user ID information.

The final piece of administration that I like to handle with this frmMakeUserLogs form is closing down the database at midnight by using the form timer event, as follows:

 Private Sub Form_Timer() ' Close the database at midnight (in case anyone has left it on). ' This step closes the database in a safe manner, saving all open ' database objects. If Hour(Time()) = 0 And Minute(Time()) < 20 Then   DoCmd.Quit acQuitSaveAll End If End Sub 

Note  

Surveillance works best if you manage your startup properties well, as described in the startup properties chapter (Chapter 2).

I like to use this method this because it leaves the database free for early morning maintenance. Now I will discuss how to conduct surveillance at the object level.