Recording When Objects are Opened

Another important surveillance technique that I have used is to log the Windows user ID and time when a person opens an Access form or report. This information is useful as the precursor to implementing more stringent security measures because it can help you diagnose if you have a problem in the first place.

So how and why will you do this? To make the transition to object logging easy, I've created two functions that mimic the DoCmd.OpenForm and DoCmd.OpenReport methods . You use these functions to log the Windows user ID and the current time when users open the form or the report. The best way to illustrate this function is first to show the old VBA code, as follows :

 Docmd.OpenForm "AnotherForm", acNormal Docmd.OpenReport "rptGR8_UserObjectLogs", acPreview 

The converted code follows:

 OpenForm_FX "AnotherForm", acNormal OpenReport_FX " rptGR8_UserObjectLogs", acPreview 

As you can see, there is very little difference between the two methods of opening the form and report. Once I have tested this function in a live database, I have found it relatively simple to do global find-and- replaces to change all the DoCmd.Open methods in the VBA project. We will examine these two functions in more detail later in this chapter.

User Story  

The same 25-user database that I described earlier had been earmarked for a total conversion to Visual Basic 6 and SQL Server. Converting this database was necessary because other software and databases required information from this database so its reliability was very important. The IT manager was keen to convert only the forms and reports from the database that were still in constant use. Because there were 100 reports , reducing that list to 50 would bring considerable savings in development time.

To find the active reports, I integrated the object logging software into the database and let it run for a couple of months. Then, when the IT manager attended design meetings with users, he could provide quantitative information, such as "This report has been used only once in two months." This specific information was more useful than asking users who generally answer, "We use all those reports" when questioned about the list of reports. I generally display this table in a summarized format by using the rptGR8_UserObjectLogs report (shown in Figure 6-11 ). This report shows the object and its frequency of use. It also shows the last Windows user IDs to open the form or report. This user can then assist in determining why that form or report is used.

Figure 6-11: The rptGR8_UserObjectLogs report, showing activity for forms and reports.

Using VBA Code to Log Use of Individual Access Forms and Reports

Now that you have seen how useful this object logging software can be in working out what parts of the database are being used and when, let's have a look under the hood to see what actual code was used. You can find the procedures to open and log a form and a report in the demonstration database in a module called basGR8_Startup . Now I will review each of these functions.

The OpenForm_FX Function

This function uses the same arguments as DoCmd.OpenForm to open the form and logs the Windows user ID and time when it was called. To use it, substitute OpenForm_FX for DoCmd.OpenForm and keep all the other arguments the same. The following sample VBA code shows how you can open a form called MyForm in datasheet mode and only show records from the company Acme, for example:

 OpenForm_FX "MyForm", acFormDS,,"Company='Acme'" 

After opening, the OpenForm_FX function logs the user details to the UserObjectsLogs table, as follows:

 Public Const dateTimeFmt = "dd-mmm-yyyy hh:mm" Function OpenForm_FX(formName As String, Optional viewType As Variant, _           Optional filterName As Variant, Optional WhereCondition As Variant, _           Optional DataMode As Variant, Optional WindowMode As Variant, _           Optional OpenArgs As Variant) On Error GoTo OpenForm_FX_Error If IsMissing(viewType) Then   viewType = acNormal End If If IsMissing(WhereCondition) Then   WhereCondition = Null End If If IsMissing(DataMode) Then   DataMode = acFormPropertySettings End If If IsMissing(WindowMode) Then   WindowMode = acWindowNormal End If ' Open the form by using the arguments provided. DoCmd.OpenForm formName, viewType, filterName, WhereCondition, DataMode, _                 WindowMode, OpenArgs ' Log the user information to a table. Call UserObjectLogs_FX(formName, acForm) OpenForm_FX_Exit:   Exit Function OpenForm_FX_Error:   MsgBox "Error Number { " & Err.Number & " } " & vbCrLf & vbCrLf & _    Err.Description, vbExclamation, "Problems with OpenForm_FX"   GoTo OpenForm_FX_Exit End Function 

Tip  

I have found that it is better to first use the Access DoCmd.OpenForm procedure to first test the way that the form opens before converting to the OpenForm_FX function.

From a programming viewpoint, the OpenForm_FX function is interesting because it uses the optional parameter in the functions arguments, which allows you to leave out values while calling the function by using VBA code. To handle these optional arguments, the function uses the IsMissing function to find the unused arguments and thereafter adds the default value instead. Now I will show you how to use a function that works in the same way to open a report.

The OpenReport_FX Function

To log the use of a report and the person's computer and user name , you can substitute OpenReport_FX for DoCmd.OpenReport and keep all the other arguments the same:

 OpenReport_FX "MyReport", acPreview,,"Company='Acme'" 

This code opens MyReport in preview mode and shows information only for the company Acme. After opening, it logs the user name and computer to the UserObjectsLogs table.

The Time Logging Functions

Finally, both the OpenForm_FX and OpenReport_FX functions handle logging through a subroutine called UserObjectLogs_FX . This subroutine logs an object's Windows user ID and time.

 Public Sub UserObjectLogs_FX(ObjectName As String, ObjectType As Integer) On Error Resume Next Dim UserNameStr As String, ObjectTime As Date, sqlStr As String UserNameStr = User_FX ObjectTime = Now DoCmd.SetWarnings False sqlStr = "insert into UserObjectLogs " & _  "(SystemUsername, ObjectName, ObjectType, OpenTime) " & _  " values ('" & UserNameStr & "','" & ObjectName & "'," & _  ObjectType & ", #" & Format(ObjectTime, dateTimeFmt) & "#)" DoCmd.RunSQL sqlStr DoCmd.SetWarnings True End Sub 

If you are wondering how good this function will be in identifying single record changes and deletions, you will probably guess that it is a quite a coarse logging system and may not suffice for that sort of surveillance. Nevertheless, for most systems that have only a few users, logging the open data event can be quite a revelation, especially if you find that certain people are using forms and reports that you never planned that they should use. At this stage, you really can plan the next stage of your security.

In the next section, I will explain the VBA code that you need to retrieve the different user information that I have been demonstrating for the different logging systems.