Handling ODBC Errors

by Tom van Stiphout 3. November 2013

Introduction

When you develop Access client applications with a SQL Server back-end, or any other ODBC data source, you will run into error messages from ODBC. Some of them are cryptic even to developers, let alone end users. They are also notoriously difficult to handle. For bound forms the Form_Error event gives very little information. For unbound scenarios the Errors collection is better, but you are still stuck with cryptic messages.
In this blog post we will present an integrated way to deal with ODBC errors: how to intercept them and how to transform them into user-friendly messages. The details are interesting and somewhat complex, but abstracted away for those developers who just want to get things done. 2 lines of code are all that it takes.

Example

Here is an example of such message. Using the database and Access client application in the download package I copied the "Row Guid" value from the first record into the second record and saved it:

Example Odbc Error

The text on this message is:

ODBC--call failed.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'AK_SalesOrderHeader_rowguid'. Cannot insert duplicate key in object 'SalesLT.SalesOrderHeader'. The duplicate key value is (b3339719-1b0f-4ad0-a2e4-d1d6e5e6e22b). (#2627) [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated. (#3621)

The error occurs because saving the record violates a database rule that says that the values in the rowguid field must be unique. Of course we should use best practices to ensure that we minimize errors like this from occurring. In this case the rowguid field would probably not even be on our form. In many cases we can check for upcoming violations before the record is saved, for example in the Form_BeforeUpdate event. But that still leaves the errors that fall between the cracks or that we simply did not want to check for because it would be too expensive. We typically don't want to repeat all database logic in our application. Often it is better to let the error happen and handle it gracefully.

At the end of this blog post the error will have been intercepted and transformed to:
Database error: 'Row Guid' must be unique. A record with this value already exists.

Built-in Options for Handling ODBC Errors

Access has three built-in options for handling errors. First is the "On Error" statement which sets up an error handler for your VBA procedure. In the case where we are dealing with a bound form that is being saved by navigating to another record, closing the form, or by clicking the record selector there will be no specific event procedure involved and consequently no place to setup an error handler within an event procedure. Even if there were, for example if you save the record explicitly using some VBA code or macro behind a Save button, you will see that On Error does not trap the ODBC error.

The second option is the DBEngine.Errors collection. It will be populated with errors, possibly more than one. Alas, in the above bound form scenario it is empty as well. Still we won't fully discard this option. As we will see later it can play a role in unbound scenarios.

Finally there is the Error event for both Forms and Reports. Again using the bound form scenario the Form_Error event is invoked, with DataErr set to 3146. When we look up that error using Error$(3146) we get "Application-defined or object-defined error", a far cry from the very specific ODBC error.

Hack #1: Starting a timer

Given the bound form situation with the Form_Error event, we know that Access cannot give us the exact text of the ODBC error. We have to get it ourselves, by looking for the error dialog to appear and then extracting the text from it. If we put a simple MsgBox statement in the Error event we can also see that the ODBC error occurs AFTER that event completes.

The technique we are introducing here is starting a timer in the Error event, and then in the Timer event looking for the ODBC dialog to appear. This is done using FindWindow API, which takes a class name and a window title and returns the window handle of the form, if any was found. The timer ticks every millisecond and in my testing it never ticked more than once before the dialog was found. We then stop the timer and inspect the dialog to get the error text. In a production-quality application you would want to stop the timer if after half a second or so the dialog still has not been found.

Getting the ODBC error text

Windows is a very open operating system and there are many APIs you can use to get information about its inner workings. One of them we will be using here: EnumChildWindows. this is a very cool function that takes a window handle and a pointer to a function, and will call that function for each child window, passing in some information about that child window.

EnumChildWindow diagram

VBA can create a pointer to a function using the AddressOf operator. It points to a function we wrote in modOdbcErrorHandler, named EnumChildProc. Such function is often called a "Callback"" function since Windows "calls us back" for each child window in the enumeration. This function must be in a standard module and must have a very specific declaration or Windows is likely to crash. Save your work frequently if you are working with callback functions. It is important to note that the debugger does not work in callback functions, so we made liberal use of Debug.Print to find out what's going on.

Each time the callback function is invoked, we use GetWindowText API to get the text for the child window. Sometimes we may find "OK" or "Help" for the buttons, but eventually we will find text that starts with "ODBC--" which is the actual error message we are interested in. We save this text in a module-level global variable m_strOdbcError and tell Windows to stop enumerating.

Hack #2: Displaying our own simplified message

In a previous simpler version of EnumChildProc we wrote:

 
'Callback function 
Public Function EnumChildProc(ByVal lHwnd As Long, ByVal lParam As Long) As Long 
Debug.Print "Top of EnumChildProc" 
Const MAXLEN As Integer = 500 
Dim Buffer As String * MAXLEN 
Dim lngBufferLength As Long 
Dim strWindowText As String 
lngBufferLength = GetWindowText(lHwnd, Buffer, MAXLEN) 
strWindowText = Left$(Buffer, lngBufferLength) 
Debug.Print Time$, lHwnd, strWindowText 
If Left$(strWindowText, 6) = "ODBC--" Then 
  Debug.Print "Setting my pretty error" 
  SetWindowText lHwnd, "My Pretty Error" 
End If 
EnumChildProc = (Left$(strWindowText, 6) <> "ODBC--") 'Return True to continue enumeration. 
End Function 

In line 12 we detect the ODBC error text, and in line 14 we use SetWindowText API to set the text to the message we want to show.

Alas, this does not work. SetWindowText returns success and there is no runtime error, but the text does not appear. We think the designers of the ODBC dialog did this on purpose: they did not want a hacker to specify a different error message, possibly tricking the user into undesirable behavior.

This is where our second hack comes into play. We are again taking advantage of the open architecture of Windows. The If block above is replaced by some code that gets the size of the ODBC error label, and then calls CreateWindowEx API to create a new label right on top of the other one. This label we can write text to. The new label is an integral part of the dialog so it moves with it and gets cleaned up automatically when the user clicks OK to dismiss it.

Converting Error Messages

Once we have the ODBC error message and a way to write to the ODBC error dialog, we turn to creating an appropriate user-friendly message.
Regular expressions are a good way to recognize string fragments. In our sample application we define several as well as the converted messages, for example:

 
'ODBC message and replacement 
Const FOREIGN_KEY_CONSTRAINT As String = "The .+ statement conflicted with the FOREIGN KEY constraint" 
Const FOREIGN_KEY_CONSTRAINT_MSG As String = "Database error: The value in [LABEL] does not exist in the related table." 

The ".+" part is a regular expression for "one or more characters except \n". This enables us with one expression to look for "The INSERT statement ..." and "The UPDATE statement...". [LABEL] is a placeholder we will later replace with the label associated with the control that caused the error.

Once we have determined which error was thrown, we can parse it and use the results to build a better user-friendly message.

One of the ways to do that is referring to a field by its label rather than by its field name. For example rather than using "rowguid" we should use "Row Guid". Function GetLabel does just that. If you don't get the label you are expecting it may be because your label is not associated with your field. In design mode cut the label to the clipboard, select the field, and paste. The label will now be associated. If your form is in Continuous Forms mode it may be impossible to pick up the control name, and GetLabel will return the field name. If you study the code in the SimplifiedErrorText function more closely you will see we also handle unique index violations where the index spans over multiple columns. We query built-in catalog views such as sys.indexes and sys.index_columns to get the information needed to build the error message. Alternatively we might have queried the ISO-standard INFORMATION_SCHEMA views, or your database's particular language. The resulting error message is:
Database error: 'Row Guid' must be unique. A record with this value already exists.
or
Database error: The combination of 'Order ID' and 'Product ID' must be unique. A record with these values already exists.

In the code you can also find specific support for CHECK constraints. You can see them in action for example if you enter a ShipDate that is before the OrderDate. We support [RULE] as a placeholder that can be replaced by an interpretation of the actual check constraint rule. The resulting error message is:
Database error: This record violates a database rule: Ship Date should be blank, or on or after Order Date.

Unbound Scenarios

The sample application has a form "Inline SQL Form" showing some typical scenarios where ODBC errors can happen that are not associated with bound forms, but typically with T-SQL statements you might execute yourself. In these scenarios the standard On Error statement performs well, and the Errors collection is populated. Typical code might look like this:

 
'Example of inline T-SQL 
Private Sub cmdRequiredField_Click() 
On Error GoTo Err_Handler 
Dim sql As String 
'This should fail because there are several required fields. 
sql = "INSERT INTO SalesLT_SalesOrderHeader(RevisionNumber) VALUES (1);" 
CurrentDb.Execute sql, dbFailOnError 
Exit_Handler: 
Exit Sub 
Err_Handler: 
DumpErrorsCollection 'Check Immediate window to see results. 
Select Case Me.fraErrorHandler 
    Case 1:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical 
    Case 2: 
        MsgBox SimplifiedErrorText(Me, DBEngine.Errors(0)), vbCritical 
End Select 
Resume Exit_Handler 
End Sub 

The standard error handler still throws the less-than-useful "Error 3146: ODBC--call failed." but in this case the Errors collection is populated so we can send Errors(0) to the existing SimplifiedErrorText function and we get:
Database error: Not all required fields have been filled out, including 'DueDate'.

Microsoft KB Article 206175

Years ago Microsoft published ACC2000: Cannot Trap Specific ODBC Errors on OnOpen Property of a Form. In it, Microsoft presents a different way of getting to the ODBC error information. Unfortunately it is no longer being updated and the code as written does not work for some of the new SQL Server data types like uniqueidentifier (rowguid) that are used in the AdventureWorks sample database.

The new approach presented here does not depend on data types.

Summary

In this blog post we discussed how to intercept and modify ODBC error message, which might otherwise escape detection by standard error handling, especially in bound scenarios.

In the end, virtually all of the code mentioned above can be abstracted away in a module, and all that's left for you to handle ODBC errors in bound scenarios is two lines of code:
In Form_Error:
  Response = Form_Error_Handler(Me)
In Form_Timer:
  Call Form_Timer_Handler(Me)

In unbound scenarios there is only one line of code:
  MsgBox SimplifiedErrorText(Me, DBEngine.Errors(0)), vbCritical

Additionally you should copy these modules to your application: modErrorHandler, modOdbc, and modOdbcErrorHander.

You can download a zip file with the download package here.

Comments are closed