Write error to log text file using VBA Microsoft access

Log files are plain text files that can store information temporary or more permanent. Easily we can read the details of error. A project may have many Functions in Standard Modules. Usually when we get the error in project a message will show but this message contain only limited information. For make project more developers friendly we can make common event handler. In this article we are creating a centralized error handler and error log. We are implementing this article in VBA on Microsoft Access Platform.

For creating event handler and error log firstly we have to create a form with button control as shown in Fig 1.1. And we have to write the functions for creating event handler and log file. In event handler function we have to use query name or table name which is in database. This is for testing the event handler or simply you can use anything which contains the bug.

Writw error log file using VBA. Fig-1.1


On button click we have to call the error handler and log file function. Before calling the log file function we have to create text file that contains the information of errors otherwise one can create text file or other file using VBA code. In log file function we have to specify the path of file as shown in Fig 1.2.

Writw error log file using VBA. Fig-1.2


After executing the code open the text file, it contains the error details as shown in Fig 1.3.

Writw error log file using VBA. Fig-1.3


VBA Code

Option Compare Database
Private Sub Command0_Click()
End Sub
Public Function test_errorhandler()
Dim db As Database
Dim rst As Recordset
On Error GoTo test_errorhandler_Error
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from table1")
Exit Function
logfile_handler Err, Err.Description, "test_errorhandler()", CurrentDb.Name
Resume test_errorhandler_Exit
End Function
Public Function logfile_handler(ByVal erNo As Long, ByVal erDesc As String, ByVal procName As String, ByVal dbName As String)
On Error GoTo logfile_handler_Error
Dim Log_file As String
Dim msg As String
Log_file = "Path for file\FileName.txt"
Open Log_file For Append As #1
Print #1, Now()
Print #1, "Database : " & dbName
Print #1, "Procedure: " & procName
Print #1, "Error No.: " & erNo
Print #1, "Desc. : " & erDesc
Print #1, String(40, "=")
Close #1
msg = "Procedure Name: " & procName & "Error : " & erNo & " : " & erDesc
MsgBox msg, , "logfile_handler()"
Exit Function
MsgBox Err & " : " & Err.Description, , "logfile_handler()"
Resume logfile_handler_Exit
End Function


It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.