Manufacturing Information Solutions

[mistux]

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 " & _
err mod & ":" & 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"