How do I used Error Handling and Transaction Processing in the same function?

I need a detailed outline of how to integrate DAO Error Handling and Transactions (Begin, Commit, Rollback) in the same function.
bobbatAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Here is one *example* ...***********

Public Function mImport() As Boolean

     If MsgBox("You are about to begin the Loading operation." & Chr(13) & Chr(13) & _
               "Note that if an error occurs before the operation is complete, the entire operation will be 'rolled back' (a good thing) and no records will be loaded; however, data from the previous load will remain." & Chr(13) & Chr(13) & _
               "You can also Cancel the operation once it starts, which will also roll back the entire operation." & Chr(13) & Chr(13) & _
               "Click OK to Begin the operation now or Cancel to abort the operation." _
               , 49, Me.Caption) = vbCancel Then Exit Function
   
    On Error GoTo mImport_Error
    Dim wks As DAO.Workspace
    Dim rstPmLst As DAO.Recordset
    Set wks = DBEngine.Workspaces(0)
    Set rstPmLst = CurrentDb.OpenRecordset("qryZiImpQryRunLst", dbOpenDynaset)
    DoCmd.Hourglass True
    rstPmLst.MoveFirst
    wks.BeginTrans
   
    With wks.Databases(0)
        Do
            .Execute rstPmLst![qryName], dbFailOnError  'MUST HAVE  dbFailOnError or Roll back will not occur!
            If Me.chkCancel = True Then GoTo mImport_Cancel
            rstPmLst.MoveNext
        Loop Until rstPmLst.EOF
       
    End With
   
   'Commit the entire transaction ...
    wks.CommitTrans
    MsgBox "Operation completed successfully.", 64, Me.Caption
mImport_Exit:
    Err.Clear
    rstPmLst.Close
    Set rstPmLst = Nothing
    Set wks = Nothing
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Function

mImport_Error:
    wks.Rollback
    MsgBox "An error has occured and the Import process was not completed" & Chr(13) & Chr(13) & _
           "Error Information --------------------" & Chr(13) & _
           "  Error number: " & Err.Number & Chr(13) & _
           "  Description: " & Err.Description, 48, Me.Caption
    GoTo mImport_Exit
   
mImport_Cancel:
    wks.Rollback
    GoTo mImport_Exit
End Function

************

mx
0
 
bobbatAuthor Commented:
Thank you very much, this is a great piece of code I can use in the future but do you have an example that is not so sophisticated?
Question: If the function mImport returns a boolean value, where is that value set?
Clarifications:
1. The 49 in the first MsgBox statement means to display the vbExclamation Warning Message icon (48) plus display the Display OK and Cancel buttons (1), right?

2. You do not use or need a flag variable (such as fInTrans) because "In a Microsoft Jet workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail  even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.", right?

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"If the function mImport returns a boolean value, where is that value set?"

Not all related to the Transaction procession.  It's just what the function returns - If ... I wanted it to.  It would be set to True if the Commit Trans was executed, set to False on a Rollback.

Message box ... again, not related to Trans processing.  You can do whatever for a message box - or none at all.  Again, I just grabbed some code I have, dropped out some stuff ... and posted.

"do you have an example that is not so sophisticated?"
Ahh ... well, this has all the basic elements ... not sure I can make it much simplier.

"You do not use or need a flag variable (such as fInTrans)"

flnTrans ???

mx
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
bobbatAuthor Commented:
flnTrans  I think means "Failure in Transaction"  I was looking at the Access 2002 Enterprise Developer's Handbok:
. . .
Err_Handler:
     If fInTrans = True Then
          wrkCurrent.Rollback
    Endif
. . .
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... this code is right out of an app that is in daily use, where importing (in this case) is done daily.

Not sure what else you need ?>

mx
0
 
bobbatAuthor Commented:
Could you please combine the logic  of the Sub GenericSubWithHandler() into the Function DAO_Transactions?
Function DAO_Transactions()
    On Error GoTo Err_Handler
    Dim wrkCurrent As DAO.Workspace
    Dim fInTrans As Boolean
    fInTrans = False
    Set wrkCurrent = DAO.DBEngine.Workspaces(0)
    ' . . .
    wrkCurrent.BeginTrans
        fInTrans = True
    '   (Any Series of data changes here)   
    wrkCurrent.CommitTrans
    fInTrans = False    
    '. . .
Err_Handler:
    If fInTrans = True Then
        wrkCurrent.Rollback
    End If
    '   (Further error processing)
End Function
 
Sub GenericSubWithHandler()
    '   Stub showing standard way to construct an error handler    
    On Error GoTo Err_Handler    
    '   Some code that might generate a runtime error.
ExitHere:
    Exit Sub    
Err_Handler:
    '   Error Handler
    Resume ExitHere
End Sub

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Just go ahead and use Ken's example.

mx
0
 
bobbatAuthor Commented:
OK.  Who is Ken and where is his example?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... Ken Getz >> Access 2002 Enterprise Developer's Handbok:

The code that I Posted ... does this:

It runs several Action (append) queries back to back.  The names of the queries are stored in a table (tblZiImpQryRunLst >> qryZiImpQryRunLst).  IF ... all queries - run without error ... the Transaction is committed.  IF ... any query encounters any error for any reason, the ENTIRE transaction is Rolled back - All or None - a beautiful thing.  

So ... that is the basic framework for Transaction Processing.

mx
0
 
bobbatAuthor Commented:
OK, I'll use the example in Listing 2.14 on page 60 of the Access 2002 Enterprise Developers Handbook by Paul Litwin, Ken Getz, and Mike Gunderloy (Sybex).  For those who do not have the book, it would be helpful if someone combined the logic of the Sub GenericSubWithHandler() into the Function DAO_Transactions (both shown above).
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OR ... use the example I posted ...

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.