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"
