Access Cookbook

Create a Transaction Log

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?

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