Best Practice On Error Handling and Logging 1

[OMoore]

Hi all,

I've made the mistake of bringing my access project to near completion, and now realise that I should have some way of tracking any errors that occur so that I can review and debug.

(Also, I wrote a lot of the vb code for events myself (I'm a newbie) and also realise that I have left out the "On Error GoTo ErrorHandler" codes. I'll have to go back through the forms and input these!!!)

I've searched for a good article on error logging for MS databases but cannot seem to find one.

Does anyone have any recommendations for ways to capture errors in my databases that occur while the users are using it?

Thanks,
Owen

 

[LittleSmudge]

I have an ErrorLog module that I reference in all my databases.

In all relevant Error Handlers I can then write to the ErrorLog with a whole bunch of useful information.

Date/time
CurrentUser
MSAccess ErrorNumber
MSAccess ErrorDescription
ObjectName containing code ( Form or Report name)
ProcedureName where event occured
DoubleValue
IntegerValue
stringValue

The last three are available to store 'useful info' depending on the error and were ErrorLog is called from.



'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[OMoore]

Thanks LS. The info you record looks like the type of details that could prove useful to me also.

Can you describe the error handling more? Where do you record the info?? - a table you've created. What about the module? can you paste in some code?

Talk soon.
Owen

 

[LittleSmudge]

Yes Owen,

I wasn't being deliberately vague yesterday - I was working on a computer where I only had access to the compiled .mde version of the utility, not the source code.

Today I have access to a 'early development version'.

I paste it here to help you get going - but please note it is NOT the final version. It will give you a good start - but does need 'finishing off'.

My 'final version' source code is on a machine 200 miles away which I won't get to until the weekend.

(One other note ) The conn variable in the ErrorLog parameter list enables me to specify that the ErrorLog table is in a completely different database to the front end that the user is working in.
If you just want to use the current database then the value in the parameter list becomes CurrentProject.Connection ( in ADO that is )

Public Sub ErrorLog(ErrorNumber As Long, ObjectName As String, ProcedureName As String, _
                    dblNoteNumber As Double, txtNote250 As String, conn As ADODB.Connection)
On Error GoTo Err_ErrorLog
Dim lngLastErrorId As Long
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = conn
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic

rst.Open "SELECT * FROM tblErrorLog ORDER BY ErrorLogId WHERE False "
rst.AddNew
rst!ErrWhen = Now()
rst!ErrWho = CurrentUser()
rst!ErrNo = ErrorNumber
rst!ObjName = Trim(Left(ObjectName, 50))
rst!ProcName = Trim(Left(ProcedureName, 50))
rst!NoteNumber = dblNoteNumber
rst!NoteText = txtNote250
' If NewField added here then activate
' OnError Goto ErrorNew_Newfieldname
' rst!NewfieldName = value
rst.Update
rst.Close
Set rst = Nothing
Exit_ErrorLog:
Exit Sub

Err_ErrorLog:
If Err.Number = -2147217865 Then
    ' Table tblErrorLog does not exist
    MsgBox "Please inform the administrator that the error log table does not exist." _
           , , "THIS IS IMPORTANT"
    Call MakeNewErrorTable(conn)
    Resume
ElseIf Err.Number = 3265 Then
    MsgBox "Please inform the administrator that the error log table has a field missing." _
           , , "THIS IS IMPORTANT"
    Resume Exit_ErrorLog
Else
    MsgBox "Unknown Error in mdlErrorLog : " & Err.Description, , Err.Number
    Resume Exit_ErrorLog
End If

'ErrorNew_Newfieldname:
'If Err.Number = 3265 Then
'    ' Table tblErrorLog does not contain the most recent field list
'    ' field NewField is missing
'    ' So silently add this field to the table
'    Call AddNewErrorTableField(NewField, DataType, Size, NewFieldContent,conn)
'    Resume
'Else
'    MsgBox "Unknown Error in mdlErrorLog : " & Err.Description, , Err.Number
'    Resume Exit_ErrorLog
'End If

End Sub

Private Sub MakeNewErrorTable(conn As ADODB.Connection)
    
    Dim tbl As New Table
    Dim idx As New ADOX.Index
    Dim cat As New ADOX.Catalog

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = conn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockPessimistic
    
    ' Open the Catalog.
    cat.ActiveConnection = conn
    
    tbl.Name = "tblErrorLog"
    tbl.Columns.Append "ErrorLogId", adInteger, 20
    tbl.Columns.Append "ErrWhen", adDate
    tbl.Columns.Append "ErrWho", adWChar, 50
    tbl.Columns.Append "ErrNo", adInteger
    tbl.Columns.Append "ObjName", adWChar, 50
    tbl.Columns.Append "ProcName", adWChar, 50
    tbl.Columns.Append "NoteNumber", adInteger
    tbl.Columns.Append "NoteText", adWChar, 250
    
    idx.Name = "ErrorIndex"
    idx.Columns.Append "ErrorLogId"
    idx.PrimaryKey = True
    idx.Unique = True
    tbl.Indexes.Append idx
    
    cat.Tables.Append tbl

    rst.Open "SELECT * FROM tblErrorLog"
    rst.AddNew
    rst!ErrorLogId = 1
    rst!ErrWhen = Now()
    rst!ErrWho = CurrentUser()
    rst!ErrNo = 0
    rst!ObjName = "mdlErrorLog"
    rst!ProcName = "MakeNewErrorTable"
    rst!NoteNumber = 0
    rst!NoteText = "No Error Log Found - So new on created."
    rst.Update
    rst.Close
    Set rst = Nothing

End Sub

Private Sub AddNewErrorTableField(strNewFieldName As String, intDataType As Integer _
        , intSize As Integer, NewFieldContent As Variant, conn As ADODB.Connection)
    Dim tbl As New Table
    Dim idx As New ADOX.Index
    Dim cat As New ADOX.Catalog

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = conn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockPessimistic
    ' Open the Catalog.
    cat.ActiveConnection = conn
    
    tbl.Name = "tblErrorLog"
    tbl.Columns.Append strNewFieldName, intDataType, intSize
        
    cat.Tables.Append tbl

    rst.Open "SELECT * FROM tblErrorLog"
    rst.AddNew
    rst!ErrorLogId = DMax("ErrorLogId", "tblErrorLog") + 1
    rst!ErrWhen = Now()
    rst!ErrWho = CurrentUser()
    rst!ErrNo = 0
    rst!ObjName = "mdlErrorLog"
    rst!ProcName = "MakeNewErrorTable"
    rst!NoteNumber = 0
    rst!NoteText = "New field " & strNewFieldName & " added to the table."
    rst(strNewFieldName) = NewFieldContent
    rst.Update
    rst.Close
    Set rst = Nothing

End Sub

Public Function GetLocalErrorTableContents(rst As ADODB.Recordset) As ADODB.Recordset
On Error GoTo Err_GetLocal
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockPessimistic

    rst.Open "SELECT * FROM tblErrorLog"
    Set GetLocalErrorTableContents = rst
'    rst.Close
    
Exit_GetLocal:
Exit Function

Err_GetLocal:
    If Err.Number = -2147217865 Then
        MsgBox "The ErrorLog table is missing from UtilitiesCardWeb.mde" _
             & vbCrLf & "Please inform your system administrator", , "This is important"
    Else
    ' Do one's own error log call
    End If
    Resume Exit_GetLocal

End Function

'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[JeremyNYC]

I have my error trapping method detailed on my website, in the Developers' section.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OMoore]

Guys,

Sorry for the late reply, I don't get to access the net enough.

Thanks for the help. I don't know which method to go with yet. Jeremies method on the surface looks a lot simpler but I like the idea of having all the errors logged in a table.

Before i can try out either method, I need to know where to insert the code!!!! I'm still a newbie to coding. Do I insert the code into a new module?

Thanks,
Owen

 

[JeremyNYC]

Owen,

There are plenty of reasons for taking the different approaches. I use a text file instead of a table because if there is any corruption in the database, I will still be able to look at the error log. You can, of course, just link to the error log text file and view it in access.

The first routine in the error trapping page on my site shows you how you call the errortrap routine. That goes in every routine I write. The next one is the errortrap routine itself. That goes in a module, so that it can be called from any piece or code in the database.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OMoore]

Jeremy,

I'm not at all familiar with modules and this is really the first time I'm going to try use one.
Following is the code I will enter on every routine.
Form1 is a dummy form I'll use to trial it. "Form1" will change for every different form name I have right?

============================
Public Sub Form1(fFAL As Form_form1)
'(c)Copyright 2/6/01 Jeremy Wallace
On Error GoTo Error


fFAL!cmdClose.Tag = "AllowClose"
DoCmd.Close acForm, "form1"
Forms!frmswitchboard.Visible = True
Exit Sub

Error:
Select Case Err.Number
Case Else
Call ErrorTrap(Err.Number, Err.Description, "form1")
End Select
End Sub
============================

Folowing is a new module I'll create called Errortrap.
c:\accessdatabase\ (which I've substituted for "sPathToBackEnd" which I'm not 100% on, this is the location I'll save the BRerror.txt to and C:\error\ is where I will save the .txt file to if there's an issue with saving to the origianl location such as accross a network. I don't know what freefile stands for.The help function on my home PC doesn't work so I can't look it up.

============================
Public Function ErrorTrap(iErrNum As Integer, sErrDesc As String, sProcName As String, _
Optional sFormName As String, Optional lngID As Long, Optional sSql As String)
'(c)Copyright 2/6/01 Jeremy Wallace
On Error GoTo ErrorInErrorTrap
Dim sMsg As String
Dim sFile As String
Dim iCount As Integer
Dim dNow As Date


DoCmd.SetWarnings True
Application.Echo True
DoCmd.Close acForm, "frmMessage"
dNow = Now


'add a record to the Error file
sFile = freefile
Open c:\accessdatabase\ & "BRError.txt" For Append Lock Write As #sFile
Write #sFile, dNow, sUser, sProcName, iErrNum, sErrDesc, sFormName, lngID, Nz(sSql)
Close #sFile


'show the user a message
sMsg = iErrNum & ": " & sErrDesc & vbCrLf & vbCrLf & sProcName & vbCrLf & Nz(sFormName) _
& Nz(lngID) & vbCrLf & Nz(sSql)
MsgBox sMsg, vbExclamation, "Error in " & sProcName


Call DoCmd.Hourglass(False)
Exit Function


ErrorInErrorTrap:
If Err.Number = 68 Then 'device unavail--probably b/c can't write to the remote Error file
sFile = FreeFile
Open "C:\error\BRError.txt" For Append Lock Write As #sFile
Write #sFile, dNow, sUser, sProcName, iErrNum, sErrDesc, sFormName, lngID, Nz(sSql)
Close #sFile
End If
If iCount < 2 Then
DoEvents
iCount = iCount + 1
Resume
Else
MsgBox "There has been an Error in the Bank Reconciliation Application." _
& " Please write down the following information and contact the Technical Support" _
& " in charge of this application" _
& vbCrLf & iErrNum & vbCrLf & sErrDesc & vbCrLf & sProcName & vbCrLf _
& sUser & vbCrLf & dNow & vbCrLf & sFormName & vbCrLf & lngID & vbCrLf & Nz(sSql), _
vbCritical, "Error"
End If
Call DoCmd.Hourglass(False)
End Function
============================

 

[OMoore]

Jeremy,
I tried it and didn't work. I need to sit down in work and use the help file to sort through what values I should be using.
Owen

 

[JeremyNYC]

Owen,

First, "didn't work" isn't very specific <G>. You'll have to tell us what error message you got, and what line of code gave you the error message.

Next, make the code in the first form an event handler for one of your controls--put a command button on there and make it the code that runs when you click the command button. That will make it easy to test.

Next, do you have a "frmMessage"? You'll need one.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OMoore]

Jeremy,

I got it to work using the code below.

I have some questions for you which would fill one of the very large holes in my understanding!!

1. .....Public Sub CloseFrmAccountList(fFAL As Form_frmAccountList)....... I did not use this code. What's it's purpose? Also, I know it may be a basic question, but why use "Public Sub" when the onclick event already has the "Private sub"?

2. Also, linked to the above, I did not use the following - again what's it's purpose? Is it to provide an error?
...........fFAL!cmdClose.Tag = "AllowClose"
DoCmd.Close acForm, "frmAccountList"
Forms!frmswitchboard.Visible = True"........

Code I used is:

On click event for the close button of form2. I put the line "DoCmd.OpenForm form10" to give me the error as form10 does not exist.

Private Sub closewindow_Click()
'(c)Copyright 2/6/01 Jeremy Wallace
On Error GoTo Error
DoCmd.OpenForm form10
DoCmd.Close
Exit Sub

Error:
Select Case Err.Number
Case Else
Call ErrorTrap(Err.Number, Err.Description, "form2")
End Select
End Sub

____________________________________

I put the following in a new module named errortrapping:

Public Function ErrorTrap(iErrNum As Integer, sErrDesc As String, sProcName As String, _
Optional sFormName As String, Optional lngID As Long, Optional sSql As String)
'(c)Copyright 2/6/01 Jeremy Wallace
On Error GoTo ErrorInErrorTrap
Dim sMsg As String
Dim sFile As String
Dim iCount As Integer
Dim dNow As Date


DoCmd.SetWarnings True
Application.Echo True
DoCmd.Close acForm, "frmMessage"
dNow = Now


'add a record to the Error file
sFile = FreeFile
Open "c:\access\" & "BRError.txt" For Append Lock Write As #sFile
Write #sFile, dNow, sUser, sProcName, iErrNum, sErrDesc, sFormName, lngID, Nz(sSql)
Close #sFile


'show the user a message
sMsg = iErrNum & ": " & sErrDesc & vbCrLf & vbCrLf & sProcName & vbCrLf & Nz(sFormName) _
& Nz(lngID) & vbCrLf & Nz(sSql)
MsgBox sMsg, vbExclamation, "Error in " & sProcName


Call DoCmd.Hourglass(False)
Exit Function


ErrorInErrorTrap:
If Err.Number = 68 Then 'device unavail--probably b/c can't write to the remote Error file
sFile = FreeFile
Open "C:\access\good\BRError.txt" For Append Lock Write As #sFile
Write #sFile, dNow, sUser, sProcName, iErrNum, sErrDesc, sFormName, lngID, Nz(sSql)
Close #sFile
End If
If iCount < 2 Then
DoEvents
iCount = iCount + 1
Resume
Else
MsgBox "There has been an Error in the Bank Reconciliation Application." _
& " Please write down the following information and contact the Technical Support" _
& " in charge of this application" _
& vbCrLf & iErrNum & vbCrLf & sErrDesc & vbCrLf & sProcName & vbCrLf _
& sUser & vbCrLf & dNow & vbCrLf & sFormName & vbCrLf & lngID & vbCrLf & Nz(sSql), _
vbCritical, "Error"
End If
Call DoCmd.Hourglass(False)
End Function

 

[JeremyNYC]

Owen,

Nice work. It looks like you've got it up and running. Both of the things you asked about are stuff from the application for which I originally wrote this code. I should have made the code more generic before posting it. I'll have to do that soon.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OMoore]

Thanks Jeremy.

 

[OMoore]

Jeremy,
One last question. What do the following represent:
lngID
Nz(sSql)

Thet are both written to the error file.

Thanks,
Owen

 

[CajunCenturion]

There are a couple of things that you might want to consider adding to the error handling environment.

Firstly, you might want in a module, to establish a global variable (gUseErrHandler) that can be used around your "On Error" statements. This allows you to easily turn the error handler off during development and on during deployment.
[tt]
If (gUseErrHandler = True) Then
On Error GoTo ErrorHandler
End If
[/tt]
Secondly, when calling the Error Handler, you might want more information that simply the name of the form.
[tt]
Call ErrorTrap(Err.Number, Err.Description, "form2.closewindow_Click")
[/tt]
And finally, you might want to consider using a Resume statement instead of immediately dropping out of the routine. The reason is there may be (or not) things set up during that function that need to be cleaned up when exiting the routine. Putting it all together, one general routine format that you might want to consider is the following:

Private Sub RoutineName()

   If (gUseErrorHandler) Then
      On Error Goto ErrorHandler
   End If

   <routine code>

Exit_RoutineName:

   <clean up code>

Exit Sub

ErrorHandler:

   Select Case Err.Number
      Case 1
      Case Else
         Call LogTheError (ModuleName.RoutineName, <other parameters>)
   End Select
   Resume Exit_RoutineName

End Sub

As a point of note, there are other properties inside the Err object besides the Number and Description that you might find useful.

And one note a caution, opening the error log for append is a great idea, but be aware that the larger the file gets, the longer it takes to open, and position to file pointer for writing. You should periodically archive the error log, and clean it out the active log.

I know your question is for Jeremy, but I would suggest that "lngID, Nz(sSql)" are optional parameters that can be sent to the error handler, but only when they apply. The use of optional parameters to pass other pertinent information, which can be very specific to the situation, is a great idea, and like Jeremy, highly recommend that you keep that avenue open to use.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[OMoore]

Thanks for the tip! I inserted the error code from JeremyNYC which I know works into my forms. I do have to modify forms still so I'll need to skip the error handling at times. I've tried to use the code but have got a compile error for type mismatch and the "=" sign in the first code below is being highlighted. I've entered the following code in my form:

If (gUseError = True) Then
On Error GoTo Error
End If

routine code

Exit Sub

Error:
Select Case Err.Number
Case Else
Call ErrorTrap(Err.Number, Err.Description, "frmInspection")
End Select
End Sub

I've created a new module called gUseError with the following code:

Public Function gUseError()

Set gUseError = True

End Function

thanks,
Owen

 

[JeremyNYC]

Owen,

CC's suggestion was to use a global variable. To do that, put

Public gUseError as boolean

at the top of one of your modules, just below the Option Explicit statement.

Then, in whatever form opens first in your application, put
gUseError = true

in the Load or Open event of the form.

I used to use this method, and it does make sense for some things. But what I've found is that I am more likely to want to turn off the error handling for just one routine than I am to turn it off globally. It does make sense, though, to have the option to turn it all off. When I rework my error code to make it more generic I'll include this. Thanks for the good thought on that, CC.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OMoore]

Guys,

I'll be honest in saying that I think i'm out of my depth! But saying that, I'm trying to get it to work and don't want to quit until I do!

The problem I have is that it doesn't seem to matter whether I have gUseError set to false or true in my open code. This what I have:

1) A new module created called ErrorTrapOnOff:
Public Function ErrorTrapOnOff()
Option Explicit
Public gUseError As Boolean
End Function

2) On the first form that is opened, the on open statement is:
Private Sub Form_Open(Cancel As Integer)
gUseError = True
End Sub

3) I've put in the wrong name of a form on a control that is on my main form to simulate an error.

Now, I'm a little confused from the notes above. Do I:
a) At the start of my error code on all forms put:
If (gUseError = True) Then
On Error GoTo Error
End If

If I do this the error trapping routine is bypassed and I get a runtime error as the form is misspeled (as I've deliberately simulated).

b) Put:
If (gUseError) Then
On Error GoTo Error
End If
Again the error trapping is bypassed and I get the runtime error.

or

c)Only have:
On Error GoTo Error
and not put in a) or b) as the on open event of my first form has the True/False info.

Or am I way off the beaten track?!?

Thanks,
Owen

 

[JeremyNYC]

Owen,

This is getting kind of funny <G>! Public variables are not declared as part of a function. They just sit out there. This should be what's at the top of one of your modules, the very top, with nothing above it:

Option Compare Database
Option Explicit
public gUseError as Boolean

3a and 3b will both work.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[OMoore]

Thanks Jeremy. It's good that you can see the funny side, I got it to work.
Owen