لطفا منتظر باشید ...
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.
