access and transactional commit/rollback

  • Thread starter Thread starter Steven Zillmer
  • Start date Start date
S

Steven Zillmer

I'm attempting to wrap a transacitonal boundary around a
series of database insert/update/delete statements so that
if any fail, they all roll back.

I've tried this code by using the BeginTrans, Rollback,
and Commit methods of DBEngine, and also via a workspace
object, defined via:

Dim wrkDefault As Workspace
Set wrkDefault = DBEngine.Workspaces(0)
wrkDefault.BeginTrans


The general swoop of the code is like what's shown below.
With using either DBEngine or wrkDefault as the object,
the rollback method doesn't work. The compiler doesn't
complain, and I get the MsgBox from the error handling,
but the previous successful database updates remain in
place up to the failed operation.

Do transactional boundaries work when using DoCmd.RunSQL,
or do I need to loop through a recordset and do in-place
updates and deletions in the context of a recordset?


----------------------------------------
blnContinue = True
DBEngine.BeginTrans

' several operations of like this...
if blnContinue Then
strSQL = "Delete from ComponentReleaseInstallation " & _
"where ComponentReleaseID in (Select " & _
"ComponentReleaseID from ComponentRelease where " & _
"ComponentID = " & m_lngComponentID & ")"

On Error Resume Next
DoCmd.RunSQL strSQL
If Err.Number <> 0 Then
MsgBox Err.Description
DBEngine.Rollback
blnContinue = False
Else
blnContinue = True
End If
End If

' final operation, with commit on success
If blnContinue Then
strSQL = "Delete from Component where ComponentID=" & _
m_lngComponentID

On Error Resume Next
DoCmd.RunSQL strSQL
If Err.Number <> 0 Then
MsgBox Err.Description
DBEngine.Rollback
Else
DBEngine.CommitTrans
MsgBox "Updates Successful."
End If
End If
 
Use the commit, rollback, beginTrans statments with the
workspace object.

Use currentDb.execute in place of docmd.runSql
(Change docmd.runSql strSql to
currentdb.execute strsql,dbFailOnError)

hth,
Lance
 
Thanx, Lance. This worked most handily.
I found the MS Access documentation very inadequate on the
topic...
 
Back
Top