transactions in MS Access

Question

Let's say I have the following code in a form that is triggered on some click event.

DoCmd.SetWarnings False
DoCmd.OpenQuery "AddSomeStuff"
DoCmd.OpenQuery "UpdateSomeOtherStuff"
DoCmd.OpenQuery "DeleteABunchOfCrap"
DoCmd.SetWarnings True

Can I assume that the three update queries I executed (in SQL Server) are not transactional in that they are run is separate transactions?

Answer 1

Yes, they will run in separate transactions.

Answer 2

Access can have transactions if needed however you are right in your code each transaction would be executed on its own. Anyway here is a little code sample

Public Sub Foo()
Dim wrk As Workspace
Dim db As DAO.Database
On Error GoTo Error_trap
Set wrk = DBEngine(0)(0)
Set db = wrk.OpenDatabase("mydb.mdb")
wrk.BeginTrans
    db.Execute "AddStuff"
    db.Execute "DeleteStuff"
    db.Execute "UpdateStuff"
wrk.CommitTrans
db.Close
wrk.Close
Set db = Nothing
wt wrk = Nothing
Exit Sub

Error_trap:
wrk.Rollback
MsgBox "Something went wrong!"

End Sub