Beginning Access™ 2007 VBA

11.6. 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 ...