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