Transactions in ADO?

[softwareguy74]

I need to perform 3 different queries against a table in an Access database using ADO from VB6.

I want all 3 queries to be successful. If one of them fail, I want them all to fail.

I guess this could be done via a transaction but I'm not sure how to do it.

I would appreciate any example code you can provide that initiates a transaction via ADO.

Thanks in advance..

Dan

[Lafor]

also remember not to neglect the help that comes with VB

Just in case you don't have help loaded

Here is an example of what u can find there:


These transaction methods manage transaction processing within a Connection object as follows:

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.

BeginTrans, CommitTrans, and RollbackTrans Methods Example
This 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. You can use the Rollback 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 = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
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 because this
' is a demonstration.
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