ADO Transaction Processing

[ADezii]

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.