|
vb123.com |
|
|
Home Contact Us Order our Software RSS &
Newsletter
Get Good Help
The
Workbench
Find out who has your database open, start the
correct version of Access, easy compacting and backups, change startup
options, mde compile, shutdown database
Read and
Download Garry's Blog
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 or try our new Expression Web |
Is Your Database Corrupt ?
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/