How-to: Trapping Errors from ODBC operations

Access client applications with a ODBC / SQL Server back-end, will sometimes produce error messages from ODBC. These are often rather cryptic technical messages so it is good to trap them and provide a more user friendly message.

On a bound form we can use create a Form_Error routine to deal with these.

The Form_Error subroutine in each Access form is automatically triggered by the On Error event of the Form, while it is possible to write a separate Form_Error subroutine for each form in the database, it is more practical and maintainable to place the bulk of the code in a separate Public Subroutine FormErrorHandler() and store that in an Access module.

The Form_Error subroutine can then become a 1 liner in each form where you need to trap ODBC errors.

Private Sub Form_Error(Dataerr As Integer, Response As Integer)  
   FormErrorHandler Dataerr, Response, Me ' Trap common ODBC errors
End Sub

=========

Public Sub FormErrorHandler(ByVal intErr As Integer, _
                           ByRef Response As Integer, _
                           ByRef frm As Access.Form)

Dim strDescription As String

Select Case intErr
    Case 3146
         strDescription "Database ERROR: This might be a duplicate or missing value, data too large for field or an index violation, to resolve this edit the data entered or press ESC to Undo."
         Response = acDataErrContinue 'Continue without displaying the default Access error message.
    Case 3147
         strDescription = "Error: ODBC data buffer overflow, press ESC to Undo."
         Response = acDataErrContinue
    Case 3148
         strDescription = "Error: ODBC connection failed, press ESC to Undo."
         Response = acDataErrContinue
        
    '3149  ODBC incorrect DLL.
    '3150  ODBC missing DLL.
    '3151  ODBC connection to 'Item' failed.
    '3152  ODBC incorrect driver version 'Item1'; expected version 'Item2'.
    '3153  ODBC incorrect server version 'Item1'; expected version 'Item2'.
    '3154  ODBC couldn't find DLL 'Item'.

    Case 3149 To 3154
        strDescription = "Error: Misc ODBC/DLL driver error [" & Str(intErr) & "], press ESC to undo."
        Response = acDataErrContinue
        
    Case 3155 To 3157
        strDescription = "Error: ODBC insert/Delete/Update failed, press ESC to undo[" & Str(intErr) & "]."
        Response = acDataErrContinue
    
    Case 3158
        strDescription = "Error: This record is currently locked by another user, press ESC to Undo."
        Response = acDataErrContinue
        
    Case 3159
        strDescription = "Error: Not a valid bookmark, press ESC to Undo, then run compact and repair."
        Response = acDataErrContinue

    Case 3160
        strDescription = "Error: Table is not open, press ESC to Undo."
        Response = acDataErrContinue

    Case 3161
        strDescription = "Error: Cannot decrypt file with this password, press ESC to Undo."
        Response = acDataErrContinue

    Case 3162
        strDescription = "Error: You must enter a value for this field."
        Response = acDataErrContinue
        
    Case 3163
        strDescription = "Error: Couldn't insert or paste; data too long for field, press ESC to Undo."
        Response = acDataErrContinue
        
    Case 3164
        strDescription = "Error: Couldn't update field, press ESC to Undo."
        Response = acDataErrContinue

'    Case 3165
'        strDescription = "Error: Couldn't open .INF file (linked dBASE table), press ESC to Undo."
'        Response = acDataErrContinue
        
    Case 3166
        strDescription = "Error: Missing memo file, press ESC to Undo."
        Response = acDataErrContinue

    Case 3167
        strDescription = "Error: This Record has been deleted, press ESC to Undo."
        Response = acDataErrContinue
            
    Case 3168 To 3621
        strDescription = "Unknown ODBC error occurred", vbCritical, "ODBC Error #" & Str(intErr)
        Response = acDataErrContinue
        
    Case Else
        Response = acDataErrDisplay ' Display the default Access error message.
End Select

MsgBox strDescription, vbCritical, "ODBC Error #" & Str(intErr)

End Sub

“It is the highest form of self-respect to admit our errors and mistakes and make amends for them. To make a mistake is only an error in judgment, but to adhere to it when it is discovered shows infirmity of character” ~ Dale Turner

Related:

Q206175 - Cannot Trap Specific ODBC Errors on OnOpen Property of a Form
AccessMVP - Handling ODBC Errors
Error trapping NULLs - You tried to assign the null value to a variable


 
Copyright © 1999-2024 SS64.com
Some rights reserved