In the Tip of the Week #19, we demonstrated Transaction Processing, specifically as it applies to DAO (Data Access Objects). In this week's Tip, we'll illustrate how Transaction Processing can be used within the context of ADO (ActiveX Data Objects). Please refer to the Link below if you wish to see an Overview of Transaction Processing, how it is implemented within DAO, or special issues to be aware of when utilizing Transaction Processing in general.
TheScripts Tip of the Week #19 - DAO Transaction Processing
In this Tip, we'll concentrate on the ADO aspect of this process. The below listed code shows the basic, skeletal format, for Transaction Processing in ADO:
- On Error GoTo Err_Handler
-
Dim cnn As ADODB.Connection
-
Dim blnInTrans As Boolean
-
-
blnInTrans = False 'not in a Transaction as of yet
-
Set cnn = CurrentProject.Connection
-
'...
-
-
cnn.BeginTrans
-
blnInTrans = True 'now in the beginning of a Transaction
-
'any series of data changes here
-
cnn.CommitTrans
-
blnInTrans = False 'Transactions completed without Error
-
-
'...
-
-
Exit_Handler:
-
Exit Sub
-
-
Err_Handler:
-
If blnInTrans Then 'in the midst of a Transaction
-
cnn.RollbackTrans 'Restore data to pre-Transaction state
-
End If
-
'further Error processing here
-
Resume Exit_Handler
NOTE: You can see that the code is very similar to the DAO version, except that the Transaction Processing Methods in ADO, (BeginTrans, CommitTrans, and RollbackTrans), are Methods of the Connection Object rather than the Workspace Object in DAO. In addition, the DAO RollBack Method is named RollbackTrans in ADO.
