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