Running Multiple Queries Using Transactions

Written by Garry Robinson from GR-FX Pty Limited

Sometimes it is important to ensure that multiple queries have all run correctly together before you save the results.  Microsoft Access allows you to do this through the use of transactions.  Following is some example code that runs a number of different action queries using the transaction method

Private Sub cmdPostBatch_Click()
On Error GoTo errorTransaction

'Set up all the declarations and begin the transactionsDim myWrk As DAO.Workspace, myDbs As DAO.DATABASE, sqlStr As String

Set myWrk = DBEngine.Workspaces(0)
Set myDbs = CurrentDb

myWrk.BeginTrans

' Update using a SQL string - Execute is a method that ensures that the query is run as a transaction

sqlStr = "UPDATE PaymentPeriods SET PaymentPeriods.runStatus = 'P' WHERE (((PaymentPeriods.paymentRunId)= " & [Forms]![FX_Payments]![paymentRunId] & "))"
myDbs.Execute sqlStr, dbFailOnError

' Now run an append query that is stored in the Query Container in the database

myDbs.Execute "FX_PaymentPosted", dbFailOnError

' Now run a SQL delete records statement that is declared separately
myDbs.Execute sqlClearBatch, dbFailOnError

' Now all the action queries are commited together
myWrk.CommitTrans

exitTransaction:

myDbs.Close
myWrk.Close
Exit Sub

errorTransaction:

' Something happened so all the queries are not committed
  myWrk.Rollback
  MsgBox "Transaction was not completed successfully", vbCritical, "Problem with batch run"
goto exitTransaction

end sub

As an alternative to transactions, search the Access help for 

querydef execute

and look for the DB_FAILONERROR option to the execute method.

External Links

Returning the Number of Records or Rows Affected by an Action Query with VB6/VBA

Try out our popular Access shareware Graf-FX
    http://www.vb123.com/explore