Trapping Errors: ODBC and ACCESS (1 Viewer)

Djblois

Registered User.
Local time
Today, 11:40
Joined
Jan 26, 2009
Messages
598
Currently I use a Error module to trap errors in access but I have trouble with trapping ODBC errors. I found this code but Where would I put this function so I can trap ODBC errors?


http://support.microsoft.com/kb/209855

Here is my error trapping code:

Code:
    Sub UnknownError(strSub As String, lngErrCode As Integer, strErrDesc As String, Optional strControl As String)
   
      LogError strSub, lngErrCode, strErrDesc, strControl
      DoCmd.OpenForm "frmGeneralError"
      Forms!frmGeneralError.lblError.Caption = strSub & " - " & "Error#" & lngErrCode & ": " & strErrDesc
      End
   
  End Sub

Code:
    Sub LogError(strSub As String, lngErrCode As Integer, strErrDesc As String, Optional strControl As String)
   
      Dim cnn As ADODB.Connection
      Dim strSQL As String
      
      Set cnn = CurrentProject.Connection
      
      
      strSQL = "INSERT INTO tblLog (ErrorNum, ErrMessage, UserName, ErrTime, BuildNum, CurrentSub, CurrentControl) "
      
      strSQL = strSQL & "VALUES ( " & lngErrCode & ", '" & strErrDesc & "', '" & strUserLogin _
          & "', #" & Date & "#, '" & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1") & _
          Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00") & _
          Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & "', '" & strSub & "', '" & _
          strControl & "' ) "
          
      cnn.Execute strSQL, , adExecuteNoRecords
   
  End Sub

Plus, if anyone can see any suggestions on how to log the errors better or other information I should be logging please do not hesitate.
 

Banana

split with a cherry atop.
Local time
Today, 11:40
Joined
Sep 1, 2005
Messages
6,318
In general, I write a separate error handling function specifically for ODBC and reference it whenever I have forms that uses ODBC linked tables as recordsource (use Form's OnError event) and for any procedure where I open a recordset against ODBC linked tables. My ODBC error handling function then attempts to differniate between different ODBC errors and where possible, get more information about the error from the backend (easy to do with MySQL, doable with PostgreSQL, not possible with SQL Server for the OnError event) and then return the error information back to the original procedure to decide what to do with it.

HTH.
 

Djblois

Registered User.
Local time
Today, 11:40
Joined
Jan 26, 2009
Messages
598
Sounds interesting would that work with Oracle? and do you have any ideas on how to do it in Oracle?
 

Banana

split with a cherry atop.
Local time
Today, 11:40
Joined
Sep 1, 2005
Messages
6,318
It works with any backends.

The issue I mentioned is explained in this KB article, which as mentioned SQL Server cannot provide. I can do it with MySQL because MySQL has "SHOW ERRORS" which I can call via a passthrough query and thus obtain the detailed information from MySQL and customize my response based on that information. PostgreSQL doesn't have this out of the box, but I can write a stored procedure in PostgreSQL to do basically same thing as MySQL's SHOW ERRORS does.

So, it will depend on whether Oracle has functionality similar to MySQL's SHOW ERRORS that you can call via a passthrough query. For my projects with SQL Server, where this is not available, I just issue a generic message stating different possible causes for the error, which is basically the best I can do.

I hope that helps.
 

Djblois

Registered User.
Local time
Today, 11:40
Joined
Jan 26, 2009
Messages
598
Would you mind posting your error trapping code for both situations so I can have a look. That would be very helpful and I will look at the article as well. Thank you very much.
 

Banana

split with a cherry atop.
Local time
Today, 11:40
Joined
Sep 1, 2005
Messages
6,318
This is for MySQL:

Code:
Option Compare Database
Option Explicit

Static Property Get MySQLErrorMessage(iCode As Integer) As String

   On Error GoTo MySQLErrorMessage_Error

Select Case iCode
    Case 1062
        MySQLErrorMessage = "You tried to insert a duplicate record where duplicates are not permitted. Please remove any duplicate values and retry:"
    Case 1142
    'May be associated with DAO error code 3155, 3156, 3157
        MySQLErrorMessage = "You do not have sufficient privilege to perform the actions. Please contact the adminstrator if you require the privilege:"
    Case 1364
        MySQLErrorMessage = "There are required values that weren't filled in. Please fill in all values:"
    Case Else
        MySQLErrorMessage = ""
End Select

   On Error GoTo 0
   Exit Property

MySQLErrorMessage_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MySQLErrorMessage of Module modError"

End Property

'This function is to be used for any DAO operations
Public Function GetODBCError() As String

Dim derr As DAO.Error

For Each derr In DAO.Errors
    If InStr(derr.Description, "ODBC--Call failed") = 0 Then
        GetODBCError = derr.Number & ";" & derr.Description
    End If
Next

End Function

'This function is to be used for any errors caught by form's OnError event
Public Function TrapODBCError(ErrCode As Integer) As Integer

Dim sErr As String
Dim rst As DAO.Recordset

   On Error GoTo TrapODBCError_Error

Select Case ErrCode
    'Make sure this is an ODBC error
    Case 3146, 3151, 3154 To 3157, 3231 To 3232, 3234 To 3235, 3238, 3247, 3254, 3613
        Set rst = CurrentDb.OpenRecordset("eGetError", dbOpenSnapshot)
        
        With rst
            If Not .BOF And Not .EOF Then
                .MoveFirst
                Do Until .EOF
                    sErr = sErr & MySQLErrorMessage(.Fields(1).Value) & ": "
                    sErr = sErr & .Fields(2).Value & vbCrLf
                    .MoveNext
                Loop
            End If
        End With
        MsgBox sErr, vbCritical + vbOKOnly, "Error!"
        TrapODBCError = acDataErrContinue
    'Technically, this is not a ODBC error, but requires a custom message as
    ' the default message is too confusing for users
    Case 3162
        MsgBox "The value cannot be left blank. Pleaes fill in the required value.", vbInformation + vbOKOnly, "Info required."
        TrapODBCError = acDataErrContinue
    'It's not an ODBC error.
    Case Else
        TrapODBCError = acDataErrDisplay
End Select

Set rst = Nothing

   On Error GoTo 0
   Exit Function

TrapODBCError_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TrapODBCError of Module modError"

End Function

Note that "eGetError" is just a passthrough query that contains "SHOW ERRORS;" and is sent to MySQL directly. Also note that there are two different processes, depending on where we are getting the errors from, from a DAO operation in VBA code or from form's OnError event.


This is for SQL Server:
Code:
Option Compare Database
Option Explicit

Public Function HandleODBCError(DataErr As Integer, Optional ErrSource As String, Optional ErrDesc As String) As Boolean
On Error GoTo Err_Handler

Select Case DataErr
    Case 2085, 2339, 3146, 3151, 3154, 3155, _
         3156, 3157, 3231, 3232, 3234, 3235, _
         3238, 3247, 3254, 3299, 3423, 3613
    Me.Show 1
    HandleODBCError = True
Case Else
    HandleODBCError = False
End Select

Exit_Procedure:
    On Error Resume Next
    Exit Function
Err_Handler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume Exit_Procedure
    Resume
End Function

Private Sub cmdOK_Click()
On Error GoTo Err_Handler

    Me.Hide
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume Exit_Procedure
    Resume
End Sub

Private Sub UserForm_Activate()
On Error GoTo Err_Handler

    Me.Caption = AppGlobal.ApplicationNameAndDB()

Exit_Procedure:
    On Error Resume Next
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ", " & Err.Description
    End Select
    Resume Exit_Procedure
    Resume
End Sub

Which basically displays a form with a message enumerating all possible causes (e.g. you forgot to fill in a required field, some data you put in is not valid, etc. etc.).

Note that the 2nd variant is specifically for form errors only. I handle SQL Server's errors as normal when done via code because they are accessible there.

HTH.
 

Users who are viewing this thread

Top Bottom