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

Dim 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.

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

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals