Transactions

This section details what a transaction is and how to implement a transaction from VBA code.

A transaction implements a series of changes in a single batch. The entire batch of changes either succeeds or fails as a group. Here is a simple example. Suppose that you want to make a series of updates to the database. You are updating a person’s records because of a name and address change due to marriage. You may need to update two or more tables with the new information. If one or more of the updates fails, you want the entire set of updates to fail as a group. Otherwise, you might have an incomplete set of data in the database.

The ADO Connection object allows you to implement transactions. The BeginTrans method of the Connection object begins the transaction, whereas the CommitTrans method commits the transaction to make the changes final. If the transaction fails, the RollbackTrans method of the Connection object rolls back all the changes to the underlying data that were made as part of the transaction.

Here is an example of some code you could use to implement a transaction:

  Sub TestTransaction() Dim cnConnection As New ADODB.Connection Dim cmdCommand As New ADODB.Command 'set the connection and command objects to the current connection Set cnConnection = CurrentProject.Connection cmdCommand.ActiveConnection = cnConnection On Error GoTo HandleError 'begin the transaction cnConnection.BeginTrans 'specify the first SQL Statement to execute that should not cause an error cmdCommand.CommandText = _      "UPDATE tblContacts SET FirstName = 'Test' WHERE ContactId = 1" 'execute the first SQL Statement which should not cause an error cmdCommand.Execute 'specify the second SQL Statement to execute that will cause an error cmdCommand.CommandText = _      "UPDATE tblContacts SET ContactId = 'A' WHERE ContactId = 1" 'execute the second SQL Statement which should cause an error cmdCommand.Execute 'commit the transaction if both updates were successful cnConnection.CommitTrans Exit Sub HandleError:     'rollback the transaction so all updates fail     cnConnection.RollbackTrans     MsgBox "An error occurred: " & Err.Description End Sub 

The preceding procedure uses the ADO Command and Connection object to execute a series of SQL statements against the database. The BeginTrans method indicates the beginning of the transaction. The CommitTrans method indicates that the transaction was processed without errors, and thus all the changes should be committed in the underlying data source. The RollbackTrans method is in the error handler and will roll back all changes in the event of an error, so all the updates fail together. In the previous example, the first SQL statement executed successfully, whereas the second one caused an error. The result is that the first successful change is rolled back because the second one failed.