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

Posted on 2008-11-04
Medium Priority
Last Modified: 2013-11-27
I need a detailed outline of how to integrate DAO Error Handling and Transactions (Begin, Commit, Rollback) in the same function.
Question by:bobbat
  • 6
  • 5
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 22878336
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
    With wks.Databases(0)
            .Execute rstPmLst![qryName], dbFailOnError  'MUST HAVE  dbFailOnError or Roll back will not occur!
            If Me.chkCancel = True Then GoTo mImport_Cancel
        Loop Until rstPmLst.EOF
    End With
   'Commit the entire transaction ...
    MsgBox "Operation completed successfully.", 64, Me.Caption
    Set rstPmLst = Nothing
    Set wks = Nothing
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Function

    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
    GoTo mImport_Exit
End Function



Author Comment

ID: 22879064
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?
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?

LVL 75
ID: 22879152
"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 ???

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 22879354
flnTrans  I think means "Failure in Transaction"  I was looking at the Access 2002 Enterprise Developer's Handbok:
. . .
     If fInTrans = True Then
. . .
LVL 75
ID: 22879412
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 ?>


Author Comment

ID: 22879838
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)
    ' . . .
        fInTrans = True
    '   (Any Series of data changes here)   
    fInTrans = False    
    '. . .
    If fInTrans = True Then
    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.
    Exit Sub    
    '   Error Handler
    Resume ExitHere
End Sub

Open in new window

LVL 75
ID: 22879893
Just go ahead and use Ken's example.


Author Comment

ID: 22882103
OK.  Who is Ken and where is his example?
LVL 75
ID: 22882146
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.


Author Comment

ID: 22882570
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).
LVL 75
ID: 22882578
OR ... use the example I posted ...


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question