Quickly log an error message with RunSQL() (2002/2003/2007)

The RunSQL() method of DoCmd allows you to execute SQL statements against your database. However, it can also serve as a quick and sneaky way of generating a log file. To do so, you simply pass it a SQL statement that writes to a text file, like so:

DoCmd.RunSQL("SELECT Top 1 'values' INTO [object].[file] FROM table")

In this statement, 'values' is the text you want to export to the text file. For "table", you can use any table in your current database. Consider the following function:

Function createlog(errmod As String, errstr As String) As String
    Dim filename As String
    Dim sql As String
   
    filename = "mylog_" & Format(Now, "mmddyyhhnnss") & ".txt"
   
    DoCmd.SetWarnings False
    sql = "SELECT * INTO [Text;DATABASE=C:\Temp].[" & _
       filename & "] FROM (SELECT TOP 1 'Error in " & _
       errmod & ":" & Err.Description & "' " & _
       "FROM PRODUCTS)"

    DoCmd.SetWarnings True
   
    createlog = errstr
End Function

This function accepts an error string and a module name as arguments. Then, it uses RunSQL() to create a text file in the folder C:\Temp. The following sub demonstrates how you would use this function:

Sub somethingbad()
    Dim i As Integer
    On Error Resume Next
    i = 99999999

    If Err.Number <> 0 Then
       MsgBox createlog("Sub somethingbad", _
            Err.Description)
    End If
   
    On Error GoTo 0
End Sub

This sub purposely generates an overflow error and calls the log function. The log function generated a file named: mylog_032106012851.txt with the following information:

"Expr1000"
"Error in Sub somethingbad:Overflow"