Allen Browne Error Log vba, Moved tables to SQL Server

[Kryst51]

Hi All,

I just moved my tables to an SQL server, and linked my tables to my Access 2010 front end. In this db, I have a table for error logs, and used code I found on Allen Browne's site to log errors. It worked fine when the tables were in Access, but I get an error now that the tables are in SQL.

I fixed the original error, shown in Green below. but now I get the following error:

Error 3001 Invalid Argument

This is my first go with an SQL backend, so I have absolutely no idea what I am doing, so I certainly don't know what I am doing wrong. Googling the error seems to come up with a ton of explanations that I either don't understand or that I don't think apply to my scenario.

Function ErrorLog(ByVal ErrorNum As Long, ByVal ErrorDes As String, _
                  strCallingProc As String, ModName As String, Optional bShowUser As Boolean = False) _
                  As Boolean
'--------------------------------------------
' Procedure : ErrorLog
' Author    : Allen Browne, [EMAIL="allen@allenbrowne.com"]allen@allenbrowne.com[/EMAIL]
' Date      : 8/3/2010
' Purpose   : Generic error handler.
' Arguments : ErrorNum - value of Err.Number
' ErrorDes  : value of Err.Description
' strCallingProc: name of sub|function that
'                 generated the error.
' bShowUser : optional boolean: If False,
'             suppresses display.
'
'Logs errors to table "tblErrorLogID".
'--------------------------------------------
    On Error GoTo Err_ErrorLog
    ' Purpose: Generic error handler.
    ' Logs errors to table "tblDatabaseErrorLog".
    ' Arguments: ErrorNum - value of Err.Number
    ' ErrorDes - value of Err.Description
    ' strCallingProc - name of sub|function that generated the error.
    ' bShowUser - optional boolean: If False, suppresses display.
    ' Author: Allen Browne, [EMAIL="allen@allenbrowne.com"]allen@allenbrowne.com[/EMAIL]
    Dim strMsg As String      ' String for display in MsgBox
    Dim rst As DAO.Recordset  ' The tblErrorLogID table
    Select Case ErrorNum
            'Case 0
            'Debug.Print strCallingProc & " called error 0."
            'Case 2501            ' Cancelled
            'Do nothing.
        Case 3314, 2101, 2115    ' Can't save.
            bShowUser = True
            If bShowUser Then
                strMsg = "Record cannot be saved at this time." & vbCrLf & _
                         "Complete the entry, or press <Esc> to undo."
                MsgBox strMsg, vbExclamation, strCallingProc
            End If
        Case Else
            If bShowUser Then
                strMsg = "Error " & ErrorNum & ": " & ErrorDes
                MsgBox strMsg, vbExclamation, strCallingProc
            End If
[COLOR=green][B]'Here is where I added the change[/B][/COLOR]
            Set rst = CurrentDb.OpenRecordset("tblErrorLogID", , dbAppendOnly[COLOR=green][B], dbSeeChanges)[/B][/COLOR]
            rst.AddNew
            rst![ErrorNumber] = ErrorNum
            rst![ErrorDescription] = ErrorDes
            rst![ErrorDateTime] = Now()
            rst![ErrorProcedure] = strCallingProc
            rst![ShowUser] = bShowUser
            rst![ModuleName] = ModName
            rst.Update
            rst.Close
            ErrorLog = True
    End Select
 
Exit_ErrorLog:
    Set rst = Nothing
    Exit Function
Err_ErrorLog:
    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
             "Please write down the following details:" & vbCrLf & vbCrLf & _
             "Calling Proc: " & strCallingProc & vbCrLf & _
             "Error Number " & ErrorNum & vbCrLf & ErrorDes & vbCrLf & vbCrLf & _
             "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "ErrorLog()"
    Resume Exit_ErrorLog
 
End Function

 

[Kryst51]

SOLUTION TO MY PROBLEM:

After narrowing down and narrowing down where the problem originated from, I found that there is no DAO reference..... So I then googled that for Access 2010 and read that Access 2010 does not support DAO, hence my code will not work. What I read suggested to update all code to use ADO.

Hmmmmm, this will take longer then I thought, but I am glad I found out what was wrong.

http://social.msdn.microsoft.com/Forums/en/accessdev/thread/619d8a61-8da8-4024-9830-c0d9ff171a54

 

[boblarson]

I believe you went into the lock record area with your dbSeeChanges. I do believe you need it like this:

 Set rst = CurrentDb.OpenRecordset("tblErrorLogID", , dbAppendOnly + dbSeeChanges)

 

[Kryst51]

I tried what you said and now I am getting Error 91, "object variable or with block variable not set" - earlier this led me to think about references, and how when I was setting mine I couldn't find the dao one, and I forgot to figure out why until now. I'll try any other suggestions, but I believe it is the reference that is causing the issue. It errors when it tries to set the recordset. If I am wrong then I would be really happy.

 

[boblarson]

If you are using 2007 ACCDB file format the DAO reference would be Microsoft Office 12.0 Access database engine Object Library

And if 2010 you would use the same but 14.0

 

[Kryst51]

Thanks! - I do have that checked.... Also, I looked back and I didn't read far enough in the web page that I found where they told me the exact same thing that you just did....

Back to the drawing board... Could it have something to do with me trying to update an SQL table? I think maybe I should try using an Insert query.

 

[boblarson]

If it is a linked SQL table then just create an Access query and run it. No need for the recordset really.

 

[boblarson]

Oh, and make sure the SQL Server table has a primary key or else you won't be able to update it.

 

[Kryst51]

Oh, and make sure the SQL Server table has a primary key or else you won't be able to update it.

Thanks for your help!

For completeness sake, I did build a query, and it works!

Here it is:

Function ErrorLog(ByVal ErrorNum As Long, ByVal ErrorDes As String, _
                  strCallingProc As String, ModName As String, Optional bShowUser As Boolean = False) _
                  As Boolean
'--------------------------------------------
' Procedure : ErrorLog
' Author    : Allen Browne, [EMAIL="allen@allenbrowne.com"]allen@allenbrowne.com[/EMAIL]
' Date      : 8/3/2010
' Purpose   : Generic error handler.
' Arguments : ErrorNum - value of Err.Number
' ErrorDes  : value of Err.Description
' strCallingProc: name of sub|function that
'                 generated the error.
' bShowUser : optional boolean: If False,
'             suppresses display.
'
'Logs errors to table "tblErrorLogID".
'--------------------------------------------
    On Error GoTo Err_ErrorLog
    ' Purpose: Generic error handler.
    ' Logs errors to table "tblErrorLogID".
    ' Arguments: ErrorNum - value of Err.Number
    ' ErrorDes - value of Err.Description
    ' strCallingProc - name of sub|function that generated the error.
    ' bShowUser - optional boolean: If False, suppresses display.
    ' Author: Allen Browne, [EMAIL="allen@allenbrowne.com"]allen@allenbrowne.com[/EMAIL]
    Dim strMsg As String      ' String for display in MsgBox
    'Dim rst As DAO.Recordset  ' The tblDatabaseErrorLog table
    Dim rst As String
    Dim dNow As String
    
    dNow = Now
    
    
    Select Case ErrorNum
            'Case 0
            'Debug.Print strCallingProc & " called error 0."
            'Case 2501            ' Cancelled
            'Do nothing.
        Case 3314, 2101, 2115    ' Can't save.
            bShowUser = True
            If bShowUser Then
                strMsg = "Record cannot be saved at this time." & vbCrLf & _
                         "Complete the entry, or press <Esc> to undo."
                MsgBox strMsg, vbExclamation, strCallingProc
            End If
        Case Else
            If bShowUser Then
                strMsg = "Error " & ErrorNum & ": " & ErrorDes
                MsgBox strMsg, vbExclamation, strCallingProc
            End If
                        
            rst = "INSERT INTO tblErrorLogID ( ErrorNumber, ErrorDescription," & _
            " ErrorDateTime, ErrorProcedure, ShowUser, ModuleName )" & _
            " VALUES (" & Chr(34) & ErrorNum & Chr(34) & " " & _
            " , " & Chr(34) & ErrorDes & Chr(34) & " " & _
            " , #" & dNow & "# " & _
            " , " & Chr(34) & strCallingProc & Chr(34) & " " & _
            " , " & Chr(34) & bShowUser & Chr(34) & " " & _
            " , " & Chr(34) & ModName & Chr(34) & ") "
            
            'Debug.Print rst
            
            Currentdb.Execute rst
            
            ErrorLog = True
    End Select

Exit_ErrorLog:
    Exit Function
Err_ErrorLog:
    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
             "Please write down the following details:" & vbCrLf & vbCrLf & _
             "Calling Proc: " & strCallingProc & vbCrLf & _
             "Error Number " & ErrorNum & vbCrLf & ErrorDes & vbCrLf & vbCrLf & _
             "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "ErrorLog()"
    Resume Exit_ErrorLog

End Function