Basic question on transactions

  • Thread starter Thread starter Paolo
  • Start date Start date
P

Paolo

If I run a transaction in a calling sub will the transaction roll back
if there is an error in the called sub?

Thanks

(e-mail address removed)
 
If you BeginTrans, you will later CommitTrans/RollBack.

You can pass the variables to another sub or function after the BeginTrans,
and the operations performed in the child procedure will also be committed
or rolled back. Example:

Function Main
Dim ws As DAO.Workspace
Dim db As DAO.Database
Set ws = dbEngine(0)
ws.BeginTrans
Set db = ws(0)
Call MySub(db)
ws.RollBack
Set db = Nothing
Set ws = Nothing
End Function
Function Child(db As DAO.Database)
db.Execute "DELETE FROM Table1;", dbFailOnError
End Function

However, if the other function does not operation within the procedure, it
does not rollback. It would NOT rollback if the child function was like
this:
Function Child(db As DAO.Database)
DoCmd.RunSQL "DELETE FROM Table1;"
End Function

More info on the traps when working with transactions:
Archive: Move records to another table. Copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html
 
Back
Top