Using Transactions

As discussed in Chapter 6, a transaction is a delimited set of changes that are performed on a database's schema or data. These changes increase the speed of actions that change data, and enable you to undo changes that have not yet been committed.

In DAO, transactions operate under the context of a Workspace, whereas ADO transactions operate under the context of a Connection.

Just as with a DAO transaction, you begin an ADO transaction by issuing the BeginTrans method against the Connection object. To write the transaction to disk, issue the CommitTrans method. But instead of issuing a Rollback method as you would in DAO, the ADO counterpart is the RollbackTrans method.

Not all providers support transactions, so you need to verify that the provider-defined property Transaction DDL is one of the Connection object's properties. The following line entered in the Immediate window checks the value of this property:

?CurrentProject.Connection.Properties("Transaction DDL") If no error occurs, the provider supports transactions.

The following code demonstrates a typical funds transfer transaction using an ADO transaction.

Public Sub TransferFunds()

Dim cn As ADODB.Connection Set cn = New ADODB.Connection

On Error GoTo trans_Err

With cn

.ConnectionString = CurrentProject.Connection .Open End With

'Begin the transaction cn.BeginTrans

'Withdraw funds from one account table cn.Execute "UPDATE Table1 "

'Deposit funds into another account table cn.Execute "INSERT INTO Table22 "

'Commit the transaction cn.CommitTrans trans_Exit:

'Clean up cn.Close

Set cn = Nothing Exit Sub trans_Err:

'Roll back the transaction cn.RollbackTrans Resume trans_Exit End Sub

In the above example, changes to both databases will either complete as a unit or will be rolled back as a unit.

You can nest ADO transactions, but in contrast with DAO transactions, you can return an ADO transaction's nesting position when you create it. A return value of 1 indicates that it occupies the top-level position in a virtual collection. A value of 2 indicates that it is a second-level transaction, and so on.

When you call CommitTrans or RollbackTrans, you are operating on the most recently opened transaction. To resolve higher-level transactions, you must close or roll back the current transaction.

When you call CommitTrans or RollbackTrans, the appropriate action is taken, and the transaction is closed. If you set the Connection object's Attributes property to adXactCommitRetaining, a new transaction is created after you issue CommitTrans. If you set it to adXactAbortRetaining (you can set both), the same occurs after issuing RollbackTrans.

0 0

Post a comment