Controlling Transactions

ADO supports transaction processing within a connection with the help of the BeginTrans, CommitTrans, and RollbackTrans methods on a Connection object. The general idea of implementing transaction processing in ADO is illustrated in the following simple code snippet.

Const DS = "MySqlServer"
Const DB = "Northwind"
Const DP = "SQLOLEDB"

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sConn As String

sConn = "Provider=" & DP & _
          ";Data Source=" & DS & _
          ";Initial Catalog=" & DB & _
          ";Integrated Security=SSPI;"

sSQL = "SELECT ProductID, ProductName FROM Products"

Set oConn = New ADODB.Connection
oConn.Open sConn

' Create and Open the Recordset object.
Set oRs = New ADODB.Recordset
oRs.Open sSQL, oConn, adOpenStatic, adLockOptimistic, adCmdText

If oRs.RecordCount > 1 Then
    oConn.BeginTrans
        
    oRs.MoveFirst
    Id1 = oRs("ProductID")
    Name1 = oRs("ProductName")
    oRs.MoveNext
    Id2 = oRs("ProductID")
    Name2 = oRs("ProductName")

    q = "Switch ID's of " & Name1 & " and " & Name2 & "?"
    If MsgBox(q, vbYesNo) = vbYes Then
        oRs.MoveFirst
        oRs("ProductName") = Name2
        oRs.Update
        
        oRs.MoveNext
        oRs("ProductName") = Name1
        oRs.Update
    
        If MsgBox("Save changes?", vbYesNo) = vbYes Then
            oConn.CommitTrans
        Else
            oConn.RollbackTrans
        End If
    End If
    
End If
        
     
oRs.Close
oConn.Close

Here transaction processing is used to ensure that the two records are updated as one unit of operation, and that the two product names are either interchanged or not changed at all.

For detailed discussions of transaction processing see Updating and Persisting Data.