|[ Team LiB ]|
Recipe 10.3 Create a Transaction Log
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.
To add this simple logging capability to your own database, follow these steps:
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.
|[ Team LiB ]|