[AccessD] Application Error logging

jwcolby jwcolby at colbyconsulting.com
Tue Jan 20 08:36:19 CST 2009


Sorry for the delay getting back to you.

NOTE: This stuff will not work in Access 97 due to the fact 
that 97 did not have the VBA Editor.

To use my error logging system you need to download and 
install MZ-Tools 3.0.

http://www.mztools.com/v3/mztools3.aspx

MZ-Tools provides a method of inserting an error handler 
into code.  Unfortunately there is no method of just 
blasting it into a bunch of functions, or a bunch of 
different modules so you are faced with a function by 
function retrofit.  However for new code this is not an 
issue of course.

Once you have MZ-Tools 3.0 installed, open Access and open 
any code module.  The MZ-Tools tool bar should now appear in 
the code editor window.

At the extreme right of that toolbar there is an icon 
containing a crossed hammer and wrench.  Click that icon and 
an Options dialog should open.  This is where you set up 
MZ-Tools to your liking.

Click on the Error Handler tab and insert the following code 
(watch for line wraps):

On Error GoTo Err_{PROCEDURE_NAME}

	{PROCEDURE_BODY}

Exit_{PROCEDURE_NAME}:
     On Error resume next
     Exit {PROCEDURE_TYPE}
Err_{PROCEDURE_NAME}:
     Select Case Err
     Case 0      '.insert Errors you wish to ignore here
         Resume Next
     Case Else   '.All other errors will trap
         Beep
         LogErr Err.Number, Err.Description, Erl, 
cstrModule, "{PROCEDURE_NAME}"
         Resume Exit_{PROCEDURE_NAME}
     End Select
     Resume 0    '.FOR TROUBLESHOOTING

Click on the Module Header tab and insert the following code:

'---------------------------------------------------------------------------------------
' Module    : {MODULE_NAME}
' Author    : {AUTHOR}
' Date      : {DATE}
' Purpose   :
'---------------------------------------------------------------------------------------
Const cstrModule As String = "{MODULE_NAME}"

Click the OK button to close the Options Dialog and save the 
changes.

In the VBA editor create a new module.  Save the module and 
name it basErrLog or something similar.  Watch for line wraps.


Private Declare Function GetComputerName _
Lib "kernel32" Alias "GetComputerNameA" ( _
        ByVal lpBuffer As String, nSize As Long) As Long

Private Const MAX_COMPUTERNAME_LENGTH As Long = 15&
'Type the following procedure:


Public Function CurrentMachineName() As String
On Error GoTo Err_CurrentMachineName
Dim lSize As Long
Dim sBuffer As String
     sBuffer = Space$(MAX_COMPUTERNAME_LENGTH + 1)
     lSize = Len(sBuffer)

     If GetComputerName(sBuffer, lSize) Then
         CurrentMachineName = Left$(sBuffer, lSize)
     End If

Exit_CurrentMachineName:
Exit Function
Err_CurrentMachineName:
     Select Case err
     Case 0      '.insert Errors you wish to ignore here
         Resume Next
     Case Else   '.All other errors will trap
         Beep
         MsgBox err.Description, , "Error in Function 
basWorkStationName.CurrentMachineName"
         Resume Exit_CurrentMachineName
     End Select
     Resume 0    '.FOR TROUBLESHOOTING
End Function
'To test this function, type the following line in the Debug 
window, and then press ENTER:
'Print CurrentMachineName()


In this new module insert the following code:

'---------------------------------------------------------------------------------------
' Procedure : LogErr2
' Author    : jwcolby
' Date      : 8/4/2008
' Purpose   :
'---------------------------------------------------------------------------------------
'
Function LogErr(intErrNo As Integer, strErr As String, 
Optional lngLineNo As Long = 0, Optional strModule As String 
= "", Optional strFunction As String = "", Optional 
strExtraExplanation As String = "", Optional blnSilentLog As 
Boolean = False, Optional strSilentLogMsg As String = "") As 
Integer
Dim rs As ADODB.Recordset
On Error GoTo Err_LogErr

Dim lstrErr As String
Dim lstrErrMsg As String

     LogErr = intErrNo
     lstrErrMsg = strErr
     lstrErrMsg = Replace(lstrErrMsg, "'", "")
     lstrErr = lstrErrMsg
     If Len(strExtraExplanation) > 0 Then
         lstrErr = lstrErr & vbCrLf & vbCrLf & 
strExtraExplanation
     End If

     Set rs = New ADODB.Recordset
     With rs
         On Error Resume Next
         .Open "usystblErrLog", gcnn, adOpenKeyset, 
adLockPessimistic
         If err = 0 Then
             .AddNew
             !ErrLog_No = intErrNo
             !ErrLog_Msg = lstrErr
             !ErrLog_Module = strModule
             !ErrLog_Function = strFunction
             !ErrLog_LineNo = lngLineNo
             !ErrLog_WSName = CurrentMachineName()
             !ErrLog_IDUser = gclsFW.LWSCls.UserCls.UserPEID()
             !ErrLog_FEVersion = gclsFW.pFEVersion
             .Update
             .Close
         Else
             MsgBox "No error log table available to log 
errors in"
         End If
     End With
     Set rs = Nothing

Exit_LogErr:
     On Error Resume Next
     rs.Close
     Set rs = Nothing
     Exit Function
Err_LogErr:
     Select Case err
     Case 0      '.insert Errors you wish to ignore here
         Resume Next
     Case Else   '.All other errors will trap
         Beep
         LogErr err.Number, err.Description, Erl, 
cstrModule, "LogErr"
         Resume Exit_LogErr
     End Select
     Resume 0    '.FOR TROUBLESHOOTING
End Function

Notice that my framework provides a currently logged in user 
ID as well as a FE version number.  You need to replace 
those function calls with similar code from your own system 
or just replace them with a hard coded number (0 for the ID) 
and string ("") for the FE version number.

//************************************************************************

Next you need to create a table in your BE, and link it into 
your FE.  The table needs to have the following fields:

ErrLog_ID	Autonumber
ErrLog_No	Long		Holds the Err.No
ErrLog_Msg	Memo		Holds the error message
ErrLog_Module	Text		Holds the name of the module
ErrLog_Function	Text		Name of the function
ErrLog_LineNo	long		Error line number
ErrLog_WSName	text		Workstation name
ErrLog_IDUser	long		Logged in user ID
ErrLog_Dte	dateTime	Date of error
ErrLog_Time	datetime	Time of error
ErrLog_ExtraInfo Memo		Other info about error
ErrLog_FEVersion text		FE version creating error

To use, in the code editor, place your cursor into any 
function with no error handler.  In the MZ-Tools toolbar 
click the Add Error Handler button.  An error handler will 
be inserted into that function.  Also click the Add Module 
Header button (once per module) to insert a constant at the 
top of the module which is the name of the module.

Repeat for any function that needs an error handler.

The one thing I miss about my old error handler insertion 
wizard was the ability to do this to an entire project but 
alas, it does nto exist and the author is not doing any 
enhancements to MZ-Tools.  However it works and with an hour 
or two of work you can retrofit an entire application.

Let me know if I missed anything, or there is anything you 
can't figure out.

John W. Colby
www.ColbyConsulting.com


Rocky Smolin at Beach Access Software wrote:
> John:
> 
> I'd be interested.  I have a client who was looking for something like this
> for an app one function of which runs unattended from Windows scheduler at
> night.  Supposed to email reports to his clients.  Any glitch and the thing
> stops and he comes in in the morning to find an error message on the screen.
> 
> TIA
> 
> 
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com




More information about the AccessD mailing list