• Choose a category
• All
• Classic-Fiction

By Root 2017 0

to synchronize design changes, only partial changes are propagated to the replicas, creating additional headaches. In addition, Access replication is suitable only when you anticipate a small or moderate number of updates to the same records in different replicas. If you need real-time synchronization or if you anticipate a high number of updates to the records across replicas (conflicts), you may wish to consider using the replication services built into server databases such as Microsoft SQL Server or some other system.

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

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

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

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

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