Performing Transactions in ADO

ADO supports transaction management in Microsoft® SQL Server™ 2000, allowing an application to perform explicitly and implicitly started transactions on a single connection to an instance of SQL Server. After the connection is established, a recordset is opened on the result set of a select query, using a dynamic cursor and pessimistic locking (properties of a Recordset object). After you edit or update the data, you select whether to commit the changes or cancel them. The data changed in the transaction can then be committed or rolled back.

To perform an explicit transaction in an application

1. Open a new connection to an instance of SQL Server.
   
   
2. Retrieve a recordset from an instance of SQL Server.
   
   
3. Call the BeginTrans method of the Connection object to begin the transaction.
   
   
4. Make changes to the recordset.
   
   
5. Call the CommitTrans method of the Connection object to save changes to the recordset

   Or

   Call the RollbackTrans method of the Connection object to discard changes to the recordset.

Managing a Transaction

This example shows how to use the ADO transaction methods BeginTrans, CommitTrans, and RollbackTrans to manage a transaction.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

. . . 
' Open connection.
cn.Open

' Open titles table.
rs.Open "SELECT * FROM titles", Cn, adOpenDynamic, adLockPessimistic
. . .
' Begin the transaction.
rs.MoveFirst
cn.BeginTrans

' User loops through the recordset making changes.
. . . 
' Ask if the user wants to commit all the changes made.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
   cn.CommitTrans
Else
   cn.RollbackTrans
End If

See Also

Transactions