10.3 Create a Transaction Log

10.3 Create a Transaction Log

10.3.1 Problem

You want to keep a permanent record of activities in your
database. With multiple users simultaneously changing data in your application,
how can you keep track of who made which changes?

10.3.2 Solution

Client/server databases such as Microsoft SQL Server offer
built-in transaction-logging facilities that provide both a permanent record and
a way to recover from disasters by replaying the transaction log. This solution
demonstrates a simpler transaction log using Access that tracks users and their
edits without saving all the details that would be necessary to recreate the
edits entirely.

Start Access and load 10-03.MDB. Open frmBook and add
a few records, update some existing records, and delete some records. Then
review the information in tblLog; you'll find a record in this table for each
change you made, as shown in Figure 10-15.

Figure 10-15. Examining changed records

To add this simple logging capability to your own database,
follow these steps:

1. 
   

   Create a new table, tblLog, with the fields shown in Table
   10-3.

   
   

    

   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   Table 10-3. Fields in tblLog

   Field name	Data type
   ActionDate	Date/Time
   Action	Number (Byte)
   UserName	Text
   TableName	Text
   RecordPK	Text

   
   

    



2. 
   

   Import the module basLogging from 10-03.MDB into
   your own database.



3. 
   

   Add three event procedures to each form for which you wish
   to track changes. In the sample database, these event properties are attached
   to frmBook, and are shown in Table 10-4. Substitute the name of your own table
   for tblBook, and the primary key of the table for [BookID].

   
   

    

   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   Table 10-4. Logging properties for frmBook

   Property	Value
   AfterInsert	=acbLogAdd("tblBook", [BookID])
   AfterUpdate	=acbLogUpdate("tblBook", [BookID])
   OnDelete	=acbLogDelete("tblBook", [BookID])

   
   

    

10.3.3 Discussion

Changing data through a form triggers a series of events.
This technique assigns code to each event that indicates a change has been
executed and uses that code to append a record to a logging table. You can use
the CurrentUser function to keep track of who
made the change and the Now function to record
when it was made.

Since the three types of records in the logging table are
similar, the functions are just wrappers for a single general-purpose function
that actually adds the records. This function depends on symbolic constants that
are defined in the declarations section of the basLogging module:

Private Const mconLodAdd = 1
Private Const mconLogUpdate = 2
Private Const mconLogDelete = 3

The acbLog function accepts
as arguments all of the information that needs to be stored, opens a recordset
on the log table, and then saves the information in a new record of that
recordset:

Public Function acbLog(strTableName As String, _
 varPK As Variant, intAction As Integer) As Integer

   ' Log a user action in the log table.
   
   Dim db As DAO.Database
   Dim rstLog As DAO.Recordset

   On Error GoTo HandleErr
   
   Set db = CurrentDb(  )
   Set rstLog = db.OpenRecordset( "tblLog", dbOpenDynaset, dbAppendOnly)

   With rstLog
      .AddNew
         ![UserName] = CurrentUser(  )
         ![TableName] = strTableName
         ![RecordPK] = varPK
         ![ActionDate] = Now
         ![Action] = intAction
      rstLog.Update
   End With

   rstLog.Close

   acbLog = True
   
ExitHere:
   On Error GoTo 0
   Exit Function

HandleErr:
   MsgBox "Error " & Err.Number & ": " & Err.Description, , "acbLog(  )"
   acbLog = False
   Resume ExitHere
End Function

This technique demonstrates one reason why you should allow
users to interact with your application only via Access forms: forms alone
generate events you can trap. If you let users edit data directly via a table or
query datasheet, you can't track the edits.

You could extend this technique to capture additional detail
about the records being added, updated, or deleted. You might even add extra
fields to the logging table to capture the actual data instead of just the
primary key that identifies the changed record. This allows you to completely
reconstruct the table at any point in time by inspecting the log file and making
or removing changes. The drawback to enabling this capability is that it
requires substantially more storage space, since you'll be storing a full copy
of the data every time any part of it changes.

If you wish to log a table with a compound primary key, just
replace the last parameter when calling the acbLog
functions with a concatenation of each field that makes up the primary key. For
example, to log an addition to the tblOrderDetail table with a primary key made
up of OrderId and OrderItem, you would use the following function call in the
AfterInsert event property:

=acbLogAdd("tblOrderDetail", [OrderId] & "; " & [OrderItem])

acbLog opens a recordset on
the logging table with the dbAppendOnly argument. This returns an
initially blank recordset ready to receive new records instead of a full dynaset
whose existing records can be edited. This gives you a performance boost when
you are only adding new records and do not need to pull in existing records.