ACC2000: ADO Errors Collection Does Not Contain Form Recordset Errors

When you bind a form to an ActiveX Data Objects (ADO) recordset in Microsoft Access, data errors that occur on the form are not available within the ADO connection's Errors collection.

Microsoft Access does not use ADO to handle data binding on forms; therefore, ADO is not aware of any errors that are encountered on the form.

If your application relies on the ADO Errors collection, do not bind the recordset to your form. Instead, create a blank form with unbound controls, and then use ADO to populate the controls on the form with data from fields in the recordset. When you need to add or edit data, call the ADO methods for doing this directly.

This technique allows you to use the ADO Errors collection; however, it requires much more work than binding the recordset to the form directly. For example, your code will now have to handle navigation and cursor movement, moving data between form controls and recordset fields, and updating records within the recordset.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem occurs because of how Microsoft Access forms interact with recordsets. If your form is bound to an ADO recordset, Microsoft Access does not use ADO to handle updates of fields on your form. Instead, it uses the OLE DB API directly to manipulate data on bound forms.

When data-related errors occur on the form, such as duplicating a primary key, the error is passed back through the OLE DB interfaces that Microsoft Access is using. Because these OLE DB interfaces are separate from the ADO object model, the ADO connection's Errors collection is unaware that an error has occurred. Even though Microsoft Access is aware that an error has occurred, it has no way of informing ADO that the error has occurred. ADO does not currently expose a method for a client application to append errors to the connection's Errors collection.

In addition, even though the form's Error event fires whenever a data error occurs on the form, you will not be able to use it to determine the error that has occurred. The DataErr argument in the Error event procedure for Access project (ADP) forms always returns the following error:
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Steps to Reproduce the Behavior

1. CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.
   
   
2. Open the sample project NorthwindCS.adp.
3. Open the Customers form in Design view.
4. On the View menu, click Code to open the module of the form in the Visual Basic Editor.
5. Add the following code to the module of the form:

   Option Compare Database
   Option Explicit
   
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   
   Private Sub Form_Open(Cancel As Integer)
       Set cn = CurrentProject.Connection
       Set rs = New ADODB.Recordset
       With rs
           .ActiveConnection = cn
           .Source = "SELECT * FROM Customers"
           .LockType = adLockOptimistic
           .CursorType = adOpenStatic
           .Open
       End With
       Set Me.Recordset = rs
   End Sub
   
   Private Sub Form_Error(DataErr As Integer, Response As Integer)
       MsgBox "There are " & cn.Errors.Count & _
              " errors in the Errors collection of the ActiveConnection."
   End Sub
   
   Private Sub Form_Close()
       rs.Close
       Set rs = Nothing
       Set cn = Nothing
   End Sub
   						

   Note that the code in the Error event of the form reports the number of errors in the ADO connection's Errors collection.
6. On the File menu, click Close and Return to Microsoft Access.
7. On the File menu, click Save.
8. On the View menu, click Form View.
9. On the Edit menu, point to Go To, and then click New Record.
10. Enter values in the CompanyName and ContactName fields of the new record. Do not enter a value in the CustomerID field.
11. On the Records menu, click Save Record.

Note that the code in the Error event of the form fires, and displays the following message: This is followed by the actual error message returned by Microsoft SQL Server: