![]() |
![]() |
|||||||
|
Method | Description |
BeginTrans |
Begins a new transaction |
CommitTrans |
Saves any changes and ends the current transaction. It may also start a new transaction. |
RollbackTrans |
Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction. |
Syntax
level = connection.BeginTrans()
connection.BeginTrans
connection.CommitTrans
connection.RollbackTrans
Remarks
Use these methods with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit. For example, to transfer money between accounts, you subtract an amount from one and add the same amount to the other. If either update fails, the accounts no longer balance. Making these changes within an open transaction ensures either all or none of the changes goes through.Note:
Not all providers support transactions. Check that the provider-defined property "Transaction DDL" appears in the Connection object's Properties collection, indicating that the provider supports transactions. If the provider does not support transactions, calling one of these methods will return an error.
Once you call the BeginTrans method, the provider will no longer instantaneously commit any changes you make until you call CommitTrans or RollbackTrans to end the transaction.Return Value
BeginTrans can be called as a function that returns a Long variable indicating the nesting level of the transaction.Examples
This Visual Basic example changes the book type of all psychology books in the Titles table of the database. After the BeginTrans method starts a transaction that isolates all the changes made to the Titles table, the CommitTrans method saves the changes. Notice that you can use the RollbackTrans method to undo changes that you saved using the Update method.Public Sub BeginTransX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
Dim strTitle As String
Dim strMessage As String
` Open connection.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
` Open titles table.
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenDynamic
rstTitles.LockType = adLockPessimistic
rstTitles.Open "titles", cnn1, , , adCmdTable
rstTitles.MoveFirst
cnn1.BeginTrans
` Loop through recordset and ask user if she wants
` to change the type for a specified title.
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
strTitle = rstTitles!Title
strMessage = "Title: " & strTitle & vbCr & _
"Change type to self help?"
` Change the title for the specified employee.
If MsgBox(strMessage, vbYesNo) = vbYes Then
rstTitles!Type = "self_help"
rstTitles.Update
End If
End If
rstTitles.MoveNext
Loop
` Ask if the user wants to commit to all the
` changes made above.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
cnn1.CommitTrans
Else
cnn1.RollbackTrans
End If
` Print current data in recordset.
rstTitles.Requery
rstTitles.MoveFirst
Do While Not rstTitles.EOF
Debug.Print rstTitles!Title & " - " & rstTitles!Type
rstTitles.MoveNext
Loop
' Restore original data
rstTitles.MoveFirst
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "self_help" Then
rstTitles!Type = "psychology"
rstTitles.Update
End If
rstTitles.MoveNext
Loop
rstTitles.Close
cnn1.Close
End Sub
Copyright © 2000 Chili!Soft