Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Implementing Explicit Transaction Processing

Now that you are aware of the settings that affect transaction processing, you are ready to learn how to implement transaction processing. Three methods of the Connection object (covered in Chapter 14, "What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?") control transaction processing:

• BeginTrans

• CommitTrans

• RollbackTrans

The BeginTrans method of the Connection object begins the transaction loop. The moment BeginTrans is encountered, Access begins writing all changes to a log file in memory. Unless you issue the CommitTrans method of the Connection object, Jet never actually writes the changes to the database file. After the CommitTrans method is issued, Jet permanently writes the updates to the database object. If a RollbackTrans method of the Connection object is encountered, the log-in memory is released. Listing 20.1.

Listing 20.2 Transaction Processing in Access 2003 Using BeginTrans, Logging, CommitTrans, and RollbackTrans

Sub IncreaseQuantityTrans() On Error GoTo IncreaseQuantityTrans_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim boolInTrans As Boolean boolInTrans = False Set rst = New ADODB.Recordset Set cnn = CurrentProject.Connection rst.ActiveConnection = cnn rst.CursorType = adOpenKeyset rst.LockType = adLockOptimistic rst.Open "Select OrderId, Quantity From tblOrderDetails" 'Begin the Transaction Loop cnn.BeginTrans boolInTrans = True 'Loop through recordset increasing Quantity field by 1 Do Until rst.EOF rst!Quantity = rst!Quantity + 1 rst.UPDATE rst.MoveNext Loop 'Commit the Transaction; Everything went as Planned cnn.CommitTrans boolInTrans = False IncreaseQuantityTrans_Exit: Set cnn = Nothing Set rst = Nothing Exit Sub IncreaseQuantityTrans_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description 'Rollback the Transaction; An Error Occurred If boolInTrans Then cnn.RollbackTrans End If Resume IncreaseQuantityTrans_Exit End Sub

This code uses a transaction loop to ensure that everything completes as planned or not at all. Notice that the loop that moves through the recordset, increasing the Quantity field in each record by 1, is placed in a transaction loop. If all processing in the loop completes successfully, the CommitTrans method executes. If the error-handling code is encountered, the RollbackTrans method executes, ensuring that none of the changes is written to disk. The boolInTrans variable is used to determine whether the code is within the transaction loop. This ensures that the error handler performs the rollback only if an error occurs within the transaction loop. If the CommitTrans method or the RollbackTrans method is issued without an open transaction, an error occurs.