Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 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.

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].

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 enumerated
values that are defined in the declarations section of the basLogging
module:

Public Enum LogActions
Add = 1
Update = 2
Delete = 3
End Enum

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, _
Action As LogActions) 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)
rstLog.AddNew
rstLog("UserName") = CurrentUser( )
rstLog("TableName") = strTableName
rstLog("RecordPK") = varPK
rstLog("ActionDate") = Now
rstLog("Action") = Action
rstLog.Update
rstLog.Close
acbLog = True
ExitHere:
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.

10.3.4 See Also

For more information on using DAO in Access databases, see
How Do I Use Data Access Objects (DAO) in New
Databases? in the Preface.