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    
 
   
isladogs
post Jun 1 2019, 02:01 AM
Post#21


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


Hi Gatzdon

You can do further streamlining as well.
For example rather than Dim the same variables in each and every procedure, I suggest you define them once as Public variables in a standard module

CODE
    Public mProcedureName As String
    Public ErrorNumber As Long
    Public ErrorSource As String
    Public ErrorDescription As String


The obvious module to do that is the one containing your error logging procedure(s)

--------------------
Go to the top of the page
 
DanielPineault
post Jun 1 2019, 06:13 AM
Post#22


UtterAccess VIP
Posts: 6,658
Joined: 30-June 11



Every developer has their opinion on this issue. There are very long threads on this very subject if you do some Googling. So here's mine.

I place error handling everywhere. It simply make the final solution and code bulletproof. I have clients who run the native accdb, so the benefits of using an accde does not apply to them, but by error handling everything I avoid any ugly problems. Even single line procs can fail. It takes 2 seconds to insert an error handler and there is no down side. Mine logs everything to a table for later review. The entire bubbling issue also requires error handling throughout, otherwise it truly doesn't advance your cause to sporadically use error handling in a project.

QUOTE
And the runtime will not shut down for un-handled errors.

This alone is reason enough to add error handling throughout. At no time do I ever want my application suddenly closing before I didn't add an error handler. That is simply not the user experience I want to convey. An error happens (and they do), report it back nicely and continue.


At the end of the day, it is a personal choice, but to me it is simply a no-brainer, the benefit outweigh the downside (of which there are none!).

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
gatzdon
post Jun 2 2019, 12:55 PM
Post#23



Posts: 5
Joined: 7-March 19



Isladogs,

I was so focused on learning this past year. Now that you point it out, it's so obvious that I don't know why I didn't think of that sooner. Thanks for the feedback.
Go to the top of the page
 
WildBird
post Jun 3 2019, 08:04 PM
Post#24


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


Save this below as a text file, named template.txt in the same folder as your application

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

CODE
'Author:        Your Name
'Email:         YourEmail@.com
'Ph:            
'In parameters
'Output
'Description:
'Calls:
'Notes:
'Example:

On Error GoTo HandleError:
dim intMouseType as Integer
Dim strErrorMsg As String
Dim varReturn As Variant

intmousetype = screen.mousepointer

docmd.hourglass true

ExitHere:
on error resume next
'Close all recordsets etc here
varReturn = SysCmd(acSysCmdClearStatus)
screen.mousepointer = intmousetype
Exit

HandleError:
Select Case Err.Number
Case Else
|" & CurrentProject.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & erl
MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    'DoCmd.Close acForm, strUpdateForm, acSaveNo
    Resume ExitHere
End Select


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

I generally have a module named mdlCommon or something. In that I have a few bits and pieces, such as these

CODE
Sub LogError(Optional strErrorMsg As String)
'Date:      11th June 4:38 PM
'Author:    Stephen Cooper
'Email:     stephen.cooper@xxx.com.au
'Ph:        8963
'In parameters  -   strErrorMSg - A string with values of variables
'Output
'Description:   This will write to a pipe delimited text file all the errors and various
                'times, users etc.
'Notes:         'Error should have user name already.
'Example        LogError("SC5|3089 Object doesnt exist|strSQL = Delete * FROM tblNotHere")   )
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim strFileName As String
Dim strFilePath As String
Dim fso As Variant
Dim f As Variant
Dim strUserName As String

strUserName = GetUser
strFileName = "ErrorLog.txt"
strFilePath = CheckPath(GetString("ErrorPath"))

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(strFilePath & strFileName, ForAppending, True)

f.Write strUserName & "|" & strErrorMsg & "|" & Now & vbCrLf
f.Close
ExitHere:
    DoCmd.Hourglass False
    Exit Sub
HandleError:
    Select Case Err.Number
        Case Else
            MsgBox "Ironically there was an error in the error handler " & Err.Number & " " & Err.Description, vbInformation, "Error"
            Resume ExitHere
    End Select
End Sub

Function GetUser() As String
   Dim RetVal As Integer
   Dim UserName As String
   Dim Buffer As String
   Buffer = String(25, " ")
   RetVal = GetUserName(Buffer, 25)
   UserName = Strings.Left(Buffer, InStr(Buffer, Chr(0)) - 1)
   GetUser = UserName
End Function


Function GetString(ByVal strSection As String) As String
'Date:          Monday, 13 January 2014 12:52:45 PM
'Author:        Stephen Cooper
'Email:         coopers@consultant.com
'Ph:
'In parameters
'Output
'Description:
'Calls:
'Notes:
'Example:
On Error GoTo HandleError:
Dim intMouseType As Integer
Dim strErrorMsg As String
Dim varReturn As Variant
Dim intFileNo As Integer
Dim strResult As String
Dim strReadLine As String
Dim intPos As Integer
Dim strFile As String
Dim strIniName As String
DoEvents
strIniName = Left(CurrentProject.Name, InStr(CurrentProject.Name, ".") - 1) & ".ini"
strFile = CurrentProject.Path & "\" & strIniName
'Can change this to use the same name as the app, plus .ini if you want
If Dir(strFile) = "" Then
MsgBox "File " & strFile & " could not be located", vbCritical, "File Error"
GetString = ""
GoTo ExitHere
End If 'Dir(strFile) = ""
intFileNo = FreeFile
Open strFile For Input As intFileNo
Do While Not EOF(intFileNo)
    Line Input #intFileNo, strReadLine
    intPos = InStr(1, strReadLine, "=") - 1
    If intPos = 0 Then
        Exit Do
    End If 'intPos = 0
    If UCase(Trim(Left(strReadLine, intPos))) = UCase(Trim(strSection)) Then
        strResult = Mid(strReadLine, intPos + 2)
        Exit Do
    End If 'strReadLine = "[" & strSection & "]"
Loop 'While Not EOF(intFileNo)
GetString = Trim(strResult)
ExitHere:
On Error Resume Next
'Close all recordsets etc here
varReturn = SysCmd(acSysCmdClearStatus)
Screen.MousePointer = intMouseType
Close #intFileNo
Exit Function
HandleError:
Select Case Err.Number
Case Else
    LogError "GetString|" & CurrentProject.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    GetString = False
    'DoCmd.Close acForm, strUpdateForm, acSaveNo
    Resume ExitHere
End Select
End Function


Sub f(strName As String)
'Date:      Monday, 23 June 2003 4:09:50 PM
'Author:    Stephen Cooper
'Email:     stephen.cooper@xxx.com.au
'Ph:        8963
'In parameters The name of the sub
'Output
'Description: This will write a header for a sub
'Notes:
'Example
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim strErrorMsg As String
Dim mdl As Module
Dim strFileName As String
Dim strFilePath As String
Dim fso As Variant
Dim t As Variant
Dim str As String
'01/09/2003 Check for length of variable passed in
If Len(strName) = 0 Then
    MsgBox "Function must have a name!", vbInformation, "Plase supply name"
    GoTo ExitHere
End If

strFilePath = CurrentProject.Path & "\"
strFileName = "template.txt"

Set fso = CreateObject("Scripting.FileSystemObject")
Set t = fso.OpenTextFile(strFilePath & strFileName, 1, True)
Set mdl = Modules("Temp")

mdl.InsertText "Function " & strName & "() As Boolean"
mdl.InsertText "'Date:          " & Format(Now(), "dddd"", ""dd mmmm yyyy h:nn:ss AM/PM")

Do While Not t.AtEndOfStream
    str = t.ReadLine
    
    'Set Function to true by default
    If Left(Trim(str), 13) = "On Error GoTo" Then
        mdl.InsertText str & vbCrLf & vbCrLf & strName & " = True" & vbCrLf
        str = t.ReadLine
    End If 'Left(Trim(str), 5) = "Exit"
    
    If Left(Trim(str), 5) = "Exit" Then
        mdl.InsertText str & " Function"
        str = t.ReadLine
    End If 'Left(Trim(str), 5) = "Exit"
    
    If Left(Trim(str), 9) = "Case Else" Then
        mdl.InsertText str
        str = t.ReadLine
        mdl.InsertText "    LogError " & Chr(34) & strName & Trim(str)
        str = t.ReadLine
    End If 'Left(Trim(str), 9) = "LogError"
    
    If Left(Trim(str), 6) = "MsgBox" Then
        mdl.InsertText vbTab & str & vbCrLf & vbTab & strName & " = False"
        str = t.ReadLine
    End If 'Left(Trim(str), 5) = "Exit"
        
    mdl.InsertText str
Loop

mdl.InsertText vbCrLf
mdl.InsertText "End Function"

ExitHere:
    'Close all recordsets etc here
    On Error Resume Next
    DoCmd.Hourglass False
    t.Close
    Exit Sub
HandleError:
    Select Case Err.Number
        Case 9
            MsgBox "This requires that you have a Module named Temp", vbInformation, "Need Temp Module"
        Case Else
            LogError "s|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description
            MsgBox " " & Err.Number & " " & Err.Description, vbInformation, "Error"
            Resume ExitHere
    End Select
End Sub



I then have a text file, named the same as the project file, but with .ini as extension.

In this file, I can have a number of things that I dont want to store as global variables, and they can be modified outside the code. So if my project is called DataConvert.accdb then I would have a file in same folder named DataConvert.ini

CODE
ErrorPath=\\BlahBlah\Payroll Systems\07 Data Migration\03 Tools\DB


I obviously have many more lines in the file, such as backend database names, and paths etc. These can be called by using GetString() function.

So to run, in your project path, have a file called template.txt and copy text from above, editing your name etc. Have another file named same as the accdb or mdb file, with .ini extension, and add a entry for where you want your error log to go to. In your project, have a module named Temp. Optional where you put the above code, as I said I usually put it in its own module called mdlCommon or something. Name isnt important.

In immediate window, enter f"NameofFunction" and press enter. It should create a new function named NameofFunction in Temp with headings etc.

Could be a function missing, there are a lot of functions in my framework I use, so if there is any issue, let me know what is missing.





--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gemmathehusky
post Jun 4 2019, 07:39 AM
Post#25


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


This has given me some food for thought.

I still think it isn't just about handling errors per se. Of course you don't want your database to crash, lose public variables and so on, but it's also about how you identify the cause of the error, recover from the error, prevent the error arising in the first place, and even recognise that although there may be no error raised, there is actually still a problem. A lot of this needs to solved by thorough and careful database testing and design.

Let's say you are producing a pdf report, but the folder you specify in a settings table doesn't exist. So the createpdf process fails. You trap the error when the pdf fails to output, but does the error code you get at that point tell you the real cause. (I am not sure). Alternatively the query fails because a field name in a table was changed so the report doesn't open (I imagine that's just a 2501 error), and no pdf file can be produced. Maybe there is a divide by zero error causing a problem. Even more problematic - Maybe there isn't a run-time error at all, but there are some nulls in one of the data sets, meaning some rows are missing, and the report doesn't show the correct totals. You needed to report the totals for your invoices. There are actually 460 invoices, but the report only shows totals for 458 because one of the table joins didn't allow for a null. You might not even realise there was a problem.

An insidious error is that you forget to resume after an error, and just goto - so your error handler does not get reset, and a second error then causes a problem that you don't necessarily know about. Just a strange intermittent bug that you can't easily trace.

The mechanism of error handling is only a fraction of the database design problem. Hopefully your design is so robust that you never get an unchecked error, and never need to report one.

Finally, there are some issues you can't reasonable anticipate anyway. I am not sure but here's an example. eg - I had a support call this week that a find next using the "search" control next to the navigation buttons wasn't working. ("search isn't available right now"). It looked Ok to me. It turned out, the search control that was being used was in an outer unbound container form, and the user actually needed to use the "search" control in the subform, not the one in the outer container. I could have removed the navigation buttons from the container form - it had just never occurred to me. I just added a form error event to the outer form to test this event, and it turns out this is error 2137. So do you add form error events to every form to intercept potential automation errors? Once I add a form error, I think I am going to get a lot of errors showing that I don't really want to see?

fwiw, I never log errors. I get very few run time errors. I just expect users to tell me they have a problem so I can understand what they were doing at the time, and resolve the problem.


So on reflection, maybe I should log errors. But I don't think I want to log and record errors that I have anticipated and dealt with. Presumably I just want to log errors that I didn't anticipate, and haven't provided code to deal with them. Do I also therefore want a form_error event in every form to intercept all the unhandled errors?

.. and then you still need to decide which errors the user needs to be notified about at run time, which just can go in the error log table, and which need both.

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

(Gemma was my dog)
Go to the top of the page
 
WildBird
post Jun 4 2019, 10:51 PM
Post#26


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


QUOTE
I just expect users to tell me they have a problem so I can understand what they were doing at the time, and resolve the problem.

So on reflection, maybe I should log errors. But I don't think I want to log and record errors that I have anticipated and dealt with


I wont expect users to remember details of what happened, hence why I have an error log. In that, I can have things such as names of variables, SQL strings and a whole lot of other things that the user wouldnt know.

For the anticipated errors, I also exclude those in my code by using the case statement. Example is I link external files, so delete the existing one. Sometimes this table doesnt exist, so I have a case statement if 3265, resume next.

My error handling code as standard logs any error, and shows a message, but very easy to individualise it to exclude known 'errors' and also customise error messages to be logged to include variables, SQL statements etc, as well as not show any messages to user etc.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gemmathehusky
post Jun 5 2019, 03:01 AM
Post#27


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


@wildbird

QUOTE
For the anticipated errors, I also exclude those in my code by using the case statement. Example is I link external files, so delete the existing one. Sometimes this table doesnt exist, so I have a case statement if 3265, resume next.


but you wouldn't actually "log" that error, would you (I am not sure it is error 3265, but that's beside the point)
or even log it if the file was open and in-use, and couldn't be deleted?

are you not just logging unanticipated errors - therefore "bugs", if you will?

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

(Gemma was my dog)
Go to the top of the page
 
isladogs
post Jun 5 2019, 04:09 AM
Post#28


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


As I mentioned briefly back in post #7, I added error logging to every procedure combined with code to 'silently' email me the details of the error: Who/what/where/when together with details of Access/Windows versions and fitness, application version, screen resolution etc.

I did this in several of my large commercial apps precisely because end users either failed to report bugs or were so vague that I couldn't act upon that info.
I asked permission to do this and individual clients were allowed to disable the feature though none did.
Individual users were unaware the emails were being sent
Anticipated errors like 2501 didn't trigger the emails.

Whilst setting up a system of this kind was time consuming, within a very short period all long term bugs were flagged and fixed.
It also meant that I could prove to one particular and difficult client that almost all their errors were due to issues with their network which they had denied for years.

For info, Anders Ebro AKA Smiley Coder has a similar tool available for free download at http://thesmileycoder.com/crash-reporter/
His utility includes a screenshot of the error but not the email feature.

--------------------
Go to the top of the page
 
WildBird
post Jun 7 2019, 12:54 AM
Post#29


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


@gemmathehusky

QUOTE
"but you wouldn't actually "log" that error, would you (I am not sure it is error 3265, but that's beside the point)
or even log it if the file was open and in-use, and couldn't be deleted?"


The error logging function doesnt change, it is the individual functions that I can choose to change, so can ignore known 'errors'.

I am not sure what you mean by log it if the file was open and in-use, and couldnt be deleted? Its a plain text file, and I have had it open in notepad, notepad++, and it will still write to it. In years across many clients, have never had an issue with the error file.

@isladogs
Issue with emailing I have found is places change email settings and have had this cause issues, and also automating it can be troublesome with outlook for example (a program is trying to send emails on your behalf messages). I am a contractor, so when I leave a place, I dont expect or want to hear from them, you may have different relationship to clients.




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
isladogs
post Jun 7 2019, 01:06 AM
Post#30


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


Hi wildbird
I use CDO for emailing from all my Access apps unless clients insist on using other methods.
If the clients change their email settings, these will obviously need to be updated in the configuration form of the app by the program admin.

I've never needed to visit the majority of my clients sites...not even for initial installation.
The whole point of setting up error logging with email was to ensure all bugs / issues could be dealt with remotely.

--------------------
Go to the top of the page
 
gemmathehusky
post Jun 7 2019, 04:51 AM
Post#31


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


QUOTE
I am not sure what you mean by log it if the file was open and in-use, and couldnt be deleted? Its a plain text file, and I have had it open in notepad, notepad++, and it will still write to it. In years across many clients, have never had an issue with the error file.


maybe it only affects certain file types - I doubt you would be able to write to an open excel file, for instance. It was more the general point/query - about whether you would log an error that was already accounted for in the database.

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

(Gemma was my dog)
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 09:59 AM