Programming Excel with VBA and .NET

Name

connection.BeginTrans

Synopsis

Begins a transaction—a series of operations performed as a whole (committed) or canceled (rolled back). The following code wraps the code example used for the Command object’s ActiveConnection method around a transaction so that it can be committed or rolled back:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strDbPath As String
 
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
strDbPath = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & strDbPath
cnn.Open
Set cmd.ActiveConnection = cnn
cnn.BeginTrans
 
cmd.CommandText = "SELECT * FROM Employees Where EmployeeID = 9;"
Set rs = cmd.Execute
 
' Prompt user to commit all changes made
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
    cnn.CommitTrans
    Set qt = ActiveSheet.QueryTables.Add(Connection:=rs, _
    Destination:=ActiveSheet.Range("A3"))
    qt.Refresh
    ActiveSheet.Range("A1") = qt.Recordset.Source
Else
    cnn.RollbackTrans
End If
 
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing