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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Architect / Systems AnalystCommented:
"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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
OR ... use the example I posted ...

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.