|
vb123.com |
|
|
Home Contact Us Order Software Search vb123 The Magazine that Access Developers loved to read and write for is back Article Index Here or Purchase Here
RSS &
Newsletter
Get Good Help
Like FMS Products? The Toolbox About The Editor Garry
Robinson writes for a number of popular computer magazines, is now a
book author and has worked on 100+ Access databases. He is based in
Sydney, Australia
|
The
Running
Multiple Queries Using TransactionsWritten 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 transactions
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/