UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Error Handling In Every Sub?, Access 2010    
 
   
SemiAuto40
post Apr 3 2019, 08:49 AM
Post#1



Posts: 704
Joined: 3-April 12
From: L.A. (lower Alabama)


I just read information elsewhere not advocating error handling in every sub routine and function. Is this correct? Why should I or not? If my front end is .accde will un-handled errors throw users into the code of the front end?

Thanks in advance.
Go to the top of the page
 
theDBguy
post Apr 3 2019, 09:45 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Hi. There is a concept about errors "bubbling up" or "bubble up" where unhandled errors are passed to the calling procedure. Sorry, I don't have any reference for you right now.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
SemiAuto40
post Apr 3 2019, 11:17 AM
Post#3



Posts: 704
Joined: 3-April 12
From: L.A. (lower Alabama)


I like the ideal of logging error messages and details into a table - but when I get a message that tells me that the calling procedure threw some error, when actually it was a function or sub that was called by the procedure; that's when I have to wonder if not using an error handler in each and every procedure/function is not the best idea after all.

I'm hoping to get a consensus from the pros who actually develop and distribute applications on this topic.
Go to the top of the page
 
WildBird
post Apr 3 2019, 07:10 PM
Post#4


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


I generally dont use Subs, I use Functions for everything. reason being a Function can return a value, subs cant. Example, you have a procedure you need to delete a table or file. If a Sub runds, and doesnt work for some reason, you have no way of testing (well you could write extra code to check for the table maybe..., but extra work for each). As a function, you can just call
If fnDeleteTable then
DoStuff
End If

I have a Sub (maybe ironically!) that creates Functions, with built in error handling. As for writing to a table, I prefer to write to a text file. This means if the DB crashes, you can still get to the errors.

Will see if I can find a link for some other talks about this.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
AlbertKallal
post Apr 4 2019, 12:27 AM
Post#5


UtterAccess VIP
Posts: 2,851
Joined: 12-April 07
From: Edmonton, Alberta Canada


Well, keep in mind that Access will create + write subs for your events. So you dealing with zillions of them in code. I can’t say I see any advantage to writing Subs as opposed to functions, but this issue would come down to ones coding style more than anything else.


As for an application and error code stubs everywhere?

It really is a somewhat of overkill to have to write out the code stub for the error handling for these “small” 1 or 2 line code stubs (that you have lots of in a typical application).

So, for most routines, I don’t bother.

Use of local and global variables.

Of course in a given application, a BIG issue with an un-handled errors is such errors blow out your global variables, and also blow out your local variables if an un-handled error occurs.

Worse, if you are using the access runtime, then an un-handled error not only spits out an error message, but upon clicking ok the WHOLE application will now shut down.

This would suggest that if you going to use the runtime, then more aggressive error handling is required.

However, this not really the case!

If you always distribute a compiled application (an accDE), then this global + local variable blow out goes away. The problem ceases to exist! And the runtime will not shut down for un-handled errors.

So, using an accDE can VERY much improve the reliability of your application.

Any un-handled error will NEVER blow out your local or global variables (I repeat = never will).

Needless to say, you can imagine some recordset loop, and an error occurs.

Now, your counters, your reocrdset – everything blows out and is re-set (but ONLY if you using an accDB). So, your variables and even that recordset becomes invalid.

If you use an accDE, then this re-set of variables (and the shutdown in runtime) will NEVER occur.

The result is:
You don’t have to resort to tempvars etc. to persist variable values.

Your application runs like an un-stoppable freight train. You can SAFE use global vars for passwords, or connection strings. You can SAFE use class objects to store information, and NO MATTER what, they will retain their values for the duration of the Access session. So, no need for tricks like tempvars etc.

So, as a general rule:
Error handling is a good thing.
Error handling in an accDB will prevent variable re-set(both global and local vars).

But, I think spending the developer time and adopting a messy error handling routine for every little tiny code stub is a pain, waste of developer time, and not required.

But of course I trade off this time saved by ALWAYS using (distributing) a compiled accDE.

So, the big bonus of using an accDE?

Well, you don’t have to be super aggressive on error handling.

And if you by some chance do get an error, your code and VBA variables will ALWAYS remain intact, and of course you avoid that nasty runtime shutdown by NOT using accDB’s, but always using a accDE.

So you can save some money and time by not having to adopt a coding practice of having error handling code in EVERY routine.

However, I certainly not suggesting that one avoid placing error handling in important routines, but for an existing application that you want some significant reliability improvements, then it is far less effort to compile to an accDE, and distribute that. (As opposed to adding error handling to a gazillion small routines).

So, error handling is a good practice. But it not an all or nothing kind of suggesting. So it not that you must always have an error trapping code stub, and it certainly not me suggesting that you never have any. But, for these 1 or 2 line code stubs, it just seems such a waste of time to clog up, and mess up such nice clean small routines with error handling code stubs.

However, being aware of the above accDE behaviour is a good bit of knowledge, since then this knowledge opens up the door for global class or even global vars that hold things such as connection strings, or user logon information. You can at least sleep well at night that some stray error will never blow out, or re-set such variables when an un-handled error occurs. And thus this opens up the use of some application wide settings you need or want to persist.

You find thus compile trick results in rather significant increases in reliability by simply choosing and adopting a strategy to use an accDE over that of distributing an accDB.

The other bonus of course is now users can’t accident or by intention modify your forms, reports, and VBA code.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
SemiAuto40
post Apr 4 2019, 12:36 PM
Post#6



Posts: 704
Joined: 3-April 12
From: L.A. (lower Alabama)


Thank you gentlemen for your thoughtful and thorough replies hat_tip.gif I did not know that .accde would behave like you have said Albert. My significant problems have come when the .accdb crashes into code. I have been using TempVars as a work around to losing my regular variables, as I have been advised on this site by others. I don't see any disadvantage to distributing .accde and upon notification of a problem I still have my .accdb to work with and recompile to the .accde.

If I try to record into a text file I might not have access to it - or at least I don't know how or where to put it. I have been experimenting with emailing myself as errors occur in order to be able to jump on the problem immediately. The emailing uses a gmail account named like the application and some VBA code. Thus far it seems to work, but it requires descriptive emails which require more error handling code in the subs and functions. I have almost no experience using the gmail server, so I hope it does not crash, timeout, or throw an error on users computers.

I much appreciate this site and having experts to show me the way uarulez2.gif
This post has been edited by SemiAuto40: Apr 4 2019, 12:50 PM
Go to the top of the page
 
isladogs
post Apr 4 2019, 12:57 PM
Post#7


UtterAccess VIP
Posts: 1,707
Joined: 4-June 18
From: Somerset, UK


Whilst I agree that short functions of a couple of lines don't all require error handling, I don't accept Albert's comments about ACCDEs overall.
I would be interested to hear whether other experienced developers agree with all those comments.

The advice is generally to ensure you do have robust error handling whether using ACCDE files or if a runtime environment.
Having your application crash because you failed to add appropriate error handling is clearly not going to be acceptable to your clients.

Furthermore if you are building an error reporting tool to email details of errors back to yourself, then you need to add error handling to all procedures.
Having done this myself on a large application, I know how long it can take.
However, I found it very useful for eliminating application errors that I had never experienced despite working with the app for several years.


--------------------
Go to the top of the page
 
gemmathehusky
post Apr 25 2019, 09:33 AM
Post#8


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


I know this post is a few weeks old.

Generally you need error handling when you are dealing with I/O's, and something can go wrong with the records. You also need to be aware of possible overflow traps such as divide by zero and so on.
For many operations you know whatever you are doing just can't go wrong, and you don't need error handling, although you might need to be aware of anomalous results which are not RTE's per se.

I think the problem with letting errors happen is that they may then cause subsequent unanticipated errors. The code runs and completes, but gives you the wrong output.

You can get problems with logic errors though, which don't cause a run time error at all.

Generally speaking in a database you never see the whole of your data. You rely on your app being robust enough to process all the records and give the right result for that processing. If some error means that records with say for instance, a null or negative value being missed, and you don't realise that's happening, then you have problems that are both hard to pick up, and hard to debug.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
KantWin
post Apr 25 2019, 07:30 PM
Post#9



Posts: 605
Joined: 1-August 02
From: Alabama


Not contributing to the subject, but just a comment.
I've suffered from the "bubbling up" that theDBGuy mentioned, where an error is passed up to the calling routine, even when the called routine has its own error handling. I can't figure out why that would happen.

--------------------
KantWin
Go to the top of the page
 
AlbertKallal
post Apr 25 2019, 09:24 PM
Post#10


UtterAccess VIP
Posts: 2,851
Joined: 12-April 07
From: Edmonton, Alberta Canada


Yes, good comment – the bubble up issue!

For example, we often have this “one line” of code behind a button to say launch a report.

CODE
docmd.OpenReport "rptSales",acViewPreview,,"Month = "  & month(date)


Now, of course if there is no data for the report, we might want to display a message the user (as opposed to launching a blank report).

So, being smart, we use the built in event of the report called “on no data”

We put this code in that event:

CODE
Private Sub Report_NoData(Cancel As Integer)

   MsgBox "No data for this report"
   Cancel = True
  
End Sub


This is all nice and sweet, and is about the “least” amount of code we need to write to deal with a common need. (Display a message to the user as opposed to the blank report).

However, because we set the “cancel = true” in above, then the report object is never created. If we do a cancel, then we get an error message BACK in the calling code. (That simple one line of code).

This same error also occurs if we do the same for an open form, and say use the forms on-open event – check something (such as rights to open the form, or say a missing invoice number for an invoice printout), and again do a cancel = true.

And putting error handing in the report will not fix this.

So, what do I do for this?

I just go:
CODE
On Error Resume Next
docmd.OpenReport "rptSales",acViewPreview,,"Month = "  & month(date)


So our code stub now has two lines. You could add error handling, but golly gee – really?

At the end of the day?

Well, of course error handling is a good idea, good practice.

However, as you learn Access then you learn when it is a good idea to break the rule or idea to “always” have error handling.

In other words, this is never an “all” or “nothing” approach.

Simply based on your experience, make a good reasoned judgement call when you can (or should) avoid needing error code and when you can’t.

So, a simple open form (or report) will error out if the code in that report (or form) decides for some reason to “cancel out”.

So, this is a “common” bubble back issue we often encounter.

This would NOW suggest that “most” open form (or report) commands in an access application could need or use error handling.

But, for the “large” number of one line code stubs? Nah, I just put in an on error resume next.

At least if you miss one of these in your code, and use an accDE, you at least avoid blowing out all your vars if you don’t have such error handing.

But, no question – the bubble back of errors can bite you in often the simplest way as this example shows.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
gemmathehusky
post Apr 26 2019, 04:39 AM
Post#11


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


Of course, as a generic, when you cancel a report/form opening, or it just fails, you get an error 2501.

So I always add error handling when opening a form or report, because you just never know, and I really don't want unhandled errors.

For most apps, I open forms with the MS A2003 switchboard menu system, so it's a standard line of code already in my switchboard.
But I do add the error handling if I open anything from a running form.


CODE
on error goto fail
docmd.openreport "rep name"
exit sub

fail:
if err=2501 then
   msgbox "The form failed to open"

else
   msgbox "The form failed to open. " & vbcrlf & _
"Error: " & err & vbcrlf & _
"Desc: " & err.description
end if

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
JonSmith
post Apr 26 2019, 05:02 AM
Post#12


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



So I think there is nuance here and the issue is muddied because the error handling stuff really depends on what programming language / environment you are working in.

If you are working in C# or .net then bubbling up the errors is totally the way to go, the main reason is that you will get a stack of errors as the error bubbles up until it hits a handler, no information is lost and you can see exactly where the error started.
In VBA well no, you cant, the bubbling up loses information along the way making it much harder to pinpoint the method and line that started the errors.

I'm gonna disagree with Albert, both for making every line a paragraph (sorry Albert but it makes your posts really hard to read, paragraphs are a standard convention for a reason) but for advocating On Error Resume Next
QUOTE
But, for the “large” number of one line code stubs? Nah, I just put in an on error resume next.


In my opinion On Error Resume Next is only every appropriate for a code stub where the error state is part of the process, for example checking for an item in an array or collection, it can sometimes be quick to just try and read that item and catch the error to check if it exists rather than iterating through each item. A small code stub with an Resume Next there works, elsewhere, no way, it just obscures errors which is the opposite of what you want to do.


My approach, in VBA (I obviously work differently in C# and VB.net) is to add error handling and code lines to most of my code, upwards of 90% of it. I skip the smallest 1 or two line methods provided they don't call any other methods, that way if they do error I can still easily identify them as the error hasn't bubbled too far for information to be lost. All my handlers log the specific line number and the arguments for the method that caught the error. As a result my error logs are very very detailed and I can diagnose issues very quickly. The overhead is not high for me either as I use MzTools so I can add this handling through an entire application in less than 10 minutes. I have done this many times when trying to debug someone else's work where an error occurs deep in the call stack during a loop within a loop and its impossible to step through the code to find the iteration that causes the issue. This way you can just check the log and know exactly where the problem lies.


So in summary, ignore any advice to use On Error Resume Next unless its functionally part of the method. Add error handling in most places but definitely if the method can call another method to prevent bubbling more than one level because in VBA you lose all the call stack information. Make sure your error handling is highly detailed, include the line number, method arguments and method name etc.
If you do all this error tracing becomes very easy.

This is all for what I consider 'unhandled' errors, what albert and Dave are talking about in regards to catching the error when a report is blank. Thats a 'handled' error and goes on a case by case basis. You want to read a file, check if it exists first or catch the exception thrown when the file cannot be found and do x. Both of these fall outside of a 'generic' error handler which is what the OP wants. These sort of 'handled' errors should be dotted all around your code as required and implemented in a similar way Dave suggests where you filter based on the error.


I also think my approach works well since it is ported between any VBA environment. I don't like the idea of relying on .accde to handle these things, you'd need a different approach if you needed to write VBA in Excel or Outlook or if there was a limitation stopping you from using a .accde.
Go to the top of the page
 
isladogs
post Apr 26 2019, 05:22 AM
Post#13


UtterAccess VIP
Posts: 1,707
Joined: 4-June 18
From: Somerset, UK


I'm in agreement with Jon and in fact made much the same points back in post #7.
Only rarely will I use On Error Resume Next and only where there is no better way of coding in a particular procedure.

Handling error 2501 is just one example which is known in advance and easily handled.
As I also use error logging for unexpected errors, handling known 'errors' like this ensures these aren't logged
This post has been edited by isladogs: Apr 26 2019, 05:28 AM

--------------------
Go to the top of the page
 
moke123
post Apr 26 2019, 06:01 AM
Post#14



Posts: 1,366
Joined: 26-December 12
From: Berkshire Mtns.


As JonSmith mentioned MZ-Tools, I always found it to be invaluable to add generic error handling to simple procedures. Its just a click and done and simple to modify if need be.
That and the code templates make it a must have tool.
Go to the top of the page
 
gatzdon
post May 7 2019, 06:10 PM
Post#15



Posts: 5
Joined: 7-March 19



I know this post is a couple weeks old, but after building a somewhat complex record management database, I'm definitely grateful for implementing error handling in every single procedure.

As I have gotten better at coding, I have learned how to take advantage of code reuse for both forms and class modules. Knowing which line an error occurred at really doesn't help me anymore since it's difficult to discern the full context.

I made a single module for logging all errors and templated code that I copy for handling the errors, both expected and unexpected errors. I have a private constant in every module with the module name and a string variable in every procedure with the procedure name, both of which I include in the ErrorSource text. As the error bubbles up, it is logged by each procedure it bubbles through. It's easy now to look at the log and see what action was initiated by the user and which procedure triggered the error.

I started down this path because I make heavy use of transactions to ensure changes to many tables occur together or get rolled back. Without comprehensive error handling, when an unhandled error occurred, I was left with an uncommitted transaction and no object in memory pointing to it.

A nice side benefit of this approach for me is that now it is real simple to just raise an error with a custom description when a required condition is not met which rolls back the transaction and displays a message to the user. This has made it significantly easier to code complex business rules.

Go to the top of the page
 
theDBguy
post May 7 2019, 07:16 PM
Post#16


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Hi gatzdon,

Welcome to UtterAccess!
welcome2UA.gif

Thanks for sharing.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post May 7 2019, 08:35 PM
Post#17


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


QUOTE
I made a single module for logging all errors and templated code that I copy for handling the errors


What are you using for logging? A table? Text file? Other?
And when you say you copy a template? What is it that you copy, and from where?

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gatzdon
post May 29 2019, 02:24 PM
Post#18



Posts: 5
Joined: 7-March 19



Sorry, just realized someone responded to my post.

I'm still a novice, so don't worry about offending me if you have comments on my code.

I have a module that logs the error details to a table using a separate workspace. Real simple SQL Insert Statement for ErrorTimestamp; ErrorNumber; ErrorSource; ErrorDescription. My database only has a few users so don't need anything fancier.

In every module I use a constant for the Module Name
CODE
Private Const mModuleName As String = "stubTemplate"


First three lines in every procedure/function/whatever before any code
CODE
    Dim mProcedureName As String
    mProcedureName = "ProcedureTemplate"

    On Error GoTo ErrorHandler


and the end of the procedure
CODE
    Exit Sub

ErrorHandler:
    ' Need to copy error details to rollup
    Dim ErrorNumber As Long
    Dim ErrorSource As String
    Dim ErrorDescription As String

    Select Case Err.Number
    ' Case [Handle Known Error Codes]

    Case Else
        ErrorNumber = Err.Number
        ErrorSource = "Module:" & mModuleName & "; Procedure Name:" & mProcedureName
        ErrorDescription = Err.Description
    End Select

    ' Log the Error
    Call fnLogError(ErrorNumber, ErrorSource, ErrorDescription)

    ' Cleanup / Destroy Objects

    ' Raise the Error for called procedures
    Err.Raise Number:=ErrorNumber, Source:=ErrorSource, Description:=ErrorDescription

    ' Display Error Message for User initiated procedures
    DoCmd.OpenForm "frmErrorDialog", acNormal, , , , acDialog, ErrorNumber & "|" & ErrorSource & "|" & ErrorDescription

End Sub



This is my module for logging the error
CODE
'***************************************************************************
' Module:   ErrorLogFunctions
' Purpose:  Create New Error Log Entry
'
' Public Subs
'     fnLogError        Create New Error Log Entry using the arguments passed
'
' Required Parameters for Error Log
'   ErrorNumber         As Long
'   ErrorSource         As String
'   ErrorDescription    As String
'
' Notes:
'   Error Log will also capture automatically
'       TimeStamp       As Date
'
'   Error Log uses a separate Workspace so entry survives a Transaction Rollback
'
'***************************************************************************
Option Compare Database
Option Explicit

Private Const mModuleName As String = "ErrorLogFunctions"

'---------------------------------------------------------------------------------------
' Procedure:    fnLogError
' Purpose:      Creates an Error Log Entry
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' ErrorNumber       Error Number
' ErrorSource       Error Source (should minimally contain Module Name and Procedure/Function Name)
' ErrorDescription  Description should be meaningful and contain relevant details
'
'---------------------------------------------------------------------------------------
Public Sub fnLogError( _
    ErrorNumber As Long, _
    ErrorSource As String, _
    ErrorDescription As String)

    Dim mProcedureName As String
    mProcedureName = "fnLogError"
        
    On Error GoTo ErrorHandler

    Dim wrkErrorLog As Workspace
    Dim db As Database
    Dim strSQL As String
              
    ' Creating a new workspace so Error is logged even if Transactions are rolled back
    Set wrkErrorLog = Application.DefaultWorkspaceClone
    Workspaces.Append wrkErrorLog
    Set db = wrkErrorLog.OpenDatabase(CurrentDb.Name)
              
    'Construct the INSERT Statement
    strSQL = "INSERT INTO ErrorLog "
    strSQL = strSQL & "(ErrorTimeStamp"
    strSQL = strSQL & ",ErrorNumber"
    strSQL = strSQL & ",ErrorSource"
    strSQL = strSQL & ",ErrorDescription"
    strSQL = strSQL & ")"
        
    strSQL = strSQL & " VALUES "
    strSQL = strSQL & "('" & Now & "'"
    strSQL = strSQL & ",'" & ErrorNumber & "'"
    strSQL = strSQL & ",'" & ErrorSource & "'"
    strSQL = strSQL & ",'" & fnSQLApostrophe(ErrorDescription) & "'"
    strSQL = strSQL & ")"
        
    'Execute the INSERT Statement
    wrkErrorLog.BeginTrans
    db.Execute strSQL, dbFailOnError
    wrkErrorLog.CommitTrans
        
    'Cleanup
    wrkErrorLog.Close
    Set db = Nothing
    Set wrkErrorLog = Nothing
    Exit Sub
        
ErrorHandler:
  
    ' If we get here, there is a serious issue from which there is no recovering!!!!!!!!!!!!!!!!
        MsgBox "CRITICAL ERROR.  CALL DON." & Chr(13) & Chr(13) & "Error number: " & Err.Number & vbCr & Err.Description
        MsgBox "DON'T CONTINUE!!!!!  CALL DON!!!!!!" & Chr(13) & Chr(13) & "Error number: " & Err.Number & vbCr & Err.Description
End Sub
'---------------------------------------------------------------------------------------
' End Code for Procedure:    fnLogError
'---------------------------------------------------------------------------------------

Go to the top of the page
 
WildBird
post May 30 2019, 12:22 AM
Post#19


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Hey Don,

A couple of quick points about your code. You can save 2 lines of error handling code by using a default value in your table rather than append the Now().

Also, I prefer to use a text file. If the data base crashes, corrupts etc, and you have a table in it, then you lose that data potentially. As a text file, I write it as a pipe delimited, and can link to it, so it works like a table, just stored externally and easily used by other systems.

Busy right now, but will write more soon.

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gatzdon
post May 31 2019, 05:05 PM
Post#20



Posts: 5
Joined: 7-March 19



WildBird, thanks for the feedback.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 08:16 AM