Solved Export Error Report as Text File.

[HillTJ]

All, I found, what I thought was a useful bit of code in the Access 2019 Bible "Deployment" section & thought it may be useful to others. What it does is write the error code etc to a text file. Previously, I'd always appended to an error log table within access, using an append to the error log table routine, then it is usual to write a form to display the error details. Using the below technique, the error log is a text file that can be read from outside access & could always be imported into excel if you wished to filter/sort records.

The first sub 'DisplayErrorMessage' is my 'normal' print error message routine triggered by the On-Error event of the code in a module. I've then included the sub 'LogError' which came from said textbook.

I'm sure both routines can be refined/improved, but to me an error log as a text file in the same folder as the database is pretty neat. Particularly, I could imagine in the case of a deployed application, then the user/administrator could locate the error log file for inspection.

Option Compare Database
Option Explicit
Public Sub DisplayErrorMessage(ErrorNumber As Integer, CallingRoutine As String) ' my original code


MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & _
           "Error Number: " & ErrorNumber & vbCrLf & _
           "Error Source: " & CallingRoutine & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
         
     Call LogError(CallingRoutine, ErrorNumber, Err.Description) ' the new bit


End Sub
Public Sub LogError(ProcName As String, ErrNum As Integer, ErrDescription As String) ' from Access 2019 Bible
    Dim sFile As String
    Dim lFile As Long
    Dim aLogEntry(1 To 6) As String
   
    Const sLogFile = "Error.Log"
    Const sLogDelim = "|"
   
    On Error Resume Next
   
    sFile = CurrentProject.path & "\" & sLogFile
    lFile = FreeFile
   
    aLogEntry(1) = Format(Now, "yyyy-mm-dd hh:mm:ss")
    aLogEntry(2) = ErrNum
    aLogEntry(3) = ErrDescription
    aLogEntry(4) = ProcName
    aLogEntry(5) = Screen.ActiveForm.Name
    aLogEntry(6) = Screen.ActiveControl.Name
   
    Open sFile For Append As lFile
    Print #lFile, Join(aLogEntry, sLogDelim)
    Close lFile
   
End Sub

 

[HillTJ]

I may have added this thread to the wrong forum. I thought the concept was clever & intended to share with others. Should it be moved to the 'Code Repository' if worthy? Please modify as you like if it has merit. I like the concept of being able to preview the error log from outside access. Comments please.

 

[theDBguy]

Hi. Did you say Access Bible "2019?" Who wrote it? What I find more interesting is how they declared the lFile variable.

 

[Pat Hartman]

The reason for writing a text log is to avoid database errors if the BE is somehow not available. But, if you elect to use this method, you must have a way of managing the log file. You can't just keep appending to it indefinitely.

 

[The_Doc_Man]

I used a logging table with date, user, error code, and some (short text) information about where the user encountered the error. Once per month I would archive the oldest month's worth of logs (by log date) to a text file. I kept 3 months online and archived the text file per military regulations on archiving operational logs. I did that with a logging table because if the BE wasn't available, nobody should have been in the database in the first place and they would call me on the phone immediately anyway. Users tend to be impatient like that.

 

[HillTJ]

@theDBguy , refer page 1024. Written by Michael Alexander & Dick Kusleika. Wiley publication.

 

[theDBguy]

@theDBguy , refer page 1024. Written by Michael Alexander & Dick Kusleika. Wiley publication.

Thanks!

 

[arnelgp]

make an Unbound report (see rptErrorLog on the demo in design view).
there is a code in the Open event of the report that sets the
Recordsource of the report and the Controlsource of each unbound textbox.

open the report (print preview or report view).

 

[HillTJ]

@arnelgp , I already have the code i originally posted up & running. I posted the code as I thought it might be of interest as an alternate to capturing errors within access as a record in a table. Of course there are benefits of both techniques. I make no claim to the development of the code. I just liked it! Does your demo do anything different? Appreciate It.

 

[arnelgp]

it doesn't do anything.
what i made is to make a report out of your Error.Log text file.

sorry, i though you need a report for your Error.log file.
if you don't ignore my post.

 

[HillTJ]

@arnelgp , Thanks but I like the ability of being able to review the log file from outside access using notepad. Of course, one could also import into excel if they required sorting etc or attach it to an email. I hope that users of my application do not generate errors at all (if I've done my work thoroughly) & i guess this is the hope of all programmers. If an error does occur, then at least I can see it even if the application is corrupted. Food for thought.

 

[HillTJ]

@arnelgp , I had a further look & like your idea. Yes, I will add to my project so that I can run an error report from within Access too. Thanks

 

[HillTJ]

@arnelgp , today I added your design to my project. I eagerly await an error!!

 

[isladogs]

Use err.Raise (err) to trigger an error so you can test the code
e.g. err.Raise 3011

 

[HillTJ]

@isladogs, in the process of working on my project i generated several 'real' errors that were trapped, captured & reported correctly. Never have i been so happy to generate an error!. Incidentally, i just implemented your 'fhandlefile' module on a double click of a browse to control. Works really well.