Trying to insert error messages into a table

[R2D2]

I have a database that backs up a bunch of tables from our live oracle database and runs every night via a windows schedule task. I've had ongoing problems with it working some nights and not other nights, so I've decided I need to put some code into it to copy whatever errors are happening and putting them into a table. tblErrors contains 2 fields - Time and Error. When an error occurs, this code runs:

DoCmd.RunSQL "INSERT INTO tblErrors VALUES ('" & Now() & "', '" _
& "Error number " & Err.Number & ": " & Err.Description & "');"

This code runs just fine when put it outside of my error trapping (i.e. error num is 0 and there's no error description). However, I can't get it to work with a real error.

I purposely created an error on this line of code:

DoCmd.OpenQuery "qryBacup3yrAssets"

Because the query is actually spelled qryBackup3yrAssets.

As a result, visual basic gives me an error on the line of code that appends the error record to the table. The error message that pops up says:

Run-time error '3075':
Syntax error (missing operator) in query expression ''Error number 7874: Microsoft Access can't find the object 'qryBacup3yrAssets.'');'

I think it might be having problems because of the fact that the error description string contains some single quotes, which the INSERT INTO statement is having a hard time interpretting. But, I have no idea how to fix this, since it seems like some error messages will have single quotes and some won't.

Anyone know what's going on or how to fix this?

Thanks!

 

[Fizzio]

I do something similar but I just record the error Number as I have a lookup table of all error messages (There is a snippet of code here somewhere to create that table for you), the form/module in which the error occured and the user.
Instead of using a SQL, I use a custom function to log the error using a recordset. If I can dig it out, I will post it here but I know Mile-O has a more comprehensive error logging system in his Db.

 

[ghudson]

Here some code that will create a table detailing most (not all) common error numbers and
their descriptions. It is from Microsoft and I have made a few modifications to it.

'The following procedure creates a table containing many of the error codes and
'strings used or reserved by Microsoft Access and by the Microsoft Jet database
'engine. Not all error codes are included in the resulting table, as some exist
'outside the range of error codes evaluated by this procedure (0 to 4500).
    
Function AccessAndJetErrorsTable() As Boolean
On Error GoTo Err_AccessAndJetErrorsTable
    
    Dim dbs As Database, tdf As TableDef, fld As Field
    Dim rst As Recordset, lngCode As Long
    Dim strAccessErr As String
    Const conAppObjectError = "Application-defined or object-defined error"
    
    ' Delete tAccessAndJetErrors table if it already exists
    If CurrentDb.OpenRecordset("SELECT 1 FROM MSysObjects WHERE Type=1 AND Flags=0 AND Name='tAccessAndJetErrors'").RecordCount = 1 Then DoCmd.DeleteObject acTable, "tAccessAndJetErrors"
    
    ' Create Errors table with ErrorNumber and ErrorDescription fields.
    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef("tAccessAndJetErrors")
    Set fld = tdf.CreateField("ErrorCode", dbLong)
    
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("ErrorString", dbMemo)
    tdf.Fields.Append fld
    
    dbs.TableDefs.Append tdf
    ' Open recordset on Errors table.
    Set rst = dbs.OpenRecordset("tAccessAndJetErrors")
    ' Loop through error codes.
    For lngCode = 0 To 3500
        On Error Resume Next
        ' Raise each error.
        strAccessErr = AccessError(lngCode)
        DoCmd.Hourglass True
        ' Skip error numbers without associated strings.
        If strAccessErr <> "" Then

        ' Skip codes that generate application or object-defined errors.
            If strAccessErr <> conAppObjectError Then
                ' Add each error code and string to Errors table.
                rst.AddNew
                rst!ErrorCode = lngCode
                ' Append string to memo field.
                rst!ErrorString.AppendChunk strAccessErr
                rst.Update
            End If
        End If
    Next lngCode
    ' Close recordset.
    rst.Close
    DoCmd.Hourglass False
    RefreshDatabaseWindow
    
    Set dbs = Nothing
    Set tdf = Nothing
    Set fld = Nothing
    Set rst = Nothing
    
    Beep
    MsgBox "Access and Jet errors table was created.", vbInformation, "tAccessAndJetErrors"
    
AccessAndJetErrorsTable = True
    
Exit_AccessAndJetErrorsTable:
    Exit Function
    
Err_AccessAndJetErrorsTable:
    Beep
    MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf & Err.Description
    Resume Exit_AccessAndJetErrorsTable
    
End Function

HTH

 

[WayneRyan]

R2D2,

I know Time is a reserved word, Error probably is too.

DoCmd.RunSQL "INSERT INTO tblErrors (ErrorTime, ErrorDesc) " & _
             "VALUES (#" & Now() & "#, '" & _
             "Error number " & Err.Number & ": " & Err.Description & "');"

Wayne

 

[R2D2]

Thanks for all your help. I searched the forum and found a solution at This thread using a recordset rather than a RunSQL Insert Into statement.