"Christina" wrote
I am wondering if Access can do a transaction query like
SQL server. For example, I'd like to insert records to
serveral tables, if one insertion is failed, I need to
roll back the previous insertion, otherwise commit all
the insertion. Any ideas? Thank you very much for your
help.
Hi Christina,
PMFBI
In addition to what Cheryl has aptly stated,
here is a code example once provided here by
John Vinson that is as good an example as you
will get in Help I believe:
Example of TRANSACTION from John Vinson on MS newsgroup:
****************quote*******************************
'In this example, query appArchive will copy some records to another table,
'then query delArchived will delete those records that have been copied.
Private Sub cmdArchive_Click()
Dim ws As Workspace
Dim db As DAO.Database
Dim qdApp As DAO.Querydef
Dim qdDel As DAO.Querydef
Dim inTrans as Boolean
On Error GoTo Proc_Error
Set ws = dbEngine.Workspaces(0) ' the current workspace
' Start a Transaction: all queries run
' during a transaction are run together; you must Commit the
' transaction if they worked, and Rollback if they didn't,
' so either everything gets done or nothing is changed.
ws.BeginTrans
inTrans = True
Set db = CurrentDb ' reference this database
Set qdApp = db.Querydefs("appArchive") ' find the append query
qdApp.Execute dbFailOnError ' and run it
Set qdDel = db.Querydefs("delArchived") ' and the delete query
qdDel.Execute dbFailOnError
' Unlikely, but check to be sure that the queries copied and deleted
' the same number of records
If qdDel.RecordsAffected <> qdApp.RecordsAffected Then GoTo Proc_Error
Set qdApp = Nothing
Set qdDel = Nothing
' Commit the transaction to disk
ws.CommitTrans
Exit Sub
Proc_Error:
' Roll back the two queries
If InTrans Then ws.Rollback
'<use msgbox to describe the problem>
End Sub
*************unquote***********
In SQL Server you might do this within a query,
in Access you will need to use VBA code.
Apologies for butting in,
Gary Walter
