Microsoft KB Archive/202868

= ACC2000: ADO Errors Collection Does Not Contain Form Recordset Errors =

Article ID: 202868

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q202868



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
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.



CAUSE
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.



RESOLUTION
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.



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



MORE INFORMATION
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:

Run-time error 2757:

There was a problem accessing a property or method of the OLE object.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

301693 ACC2000: Form Error Event Does Not Return Native SQL Server Errors in an Access Project

Steps to Reproduce the Behavior
 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.

 Open the sample project NorthwindCS.adp. Open the Customers form in Design view. On the View menu, click Code to open the module of the form in the Visual Basic Editor.  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. </li> On the File menu, click Close and Return to Microsoft Access.</li> On the File menu, click Save.</li> On the View menu, click Form View.</li> On the Edit menu, point to Go To, and then click New Record.</li> Enter values in the CompanyName and ContactName fields of the new record. Do not enter a value in the CustomerID field.</li> On the Records menu, click Save Record.</li></ol>

Note that the code in the Error event of the form fires, and displays the following message:

There are 0 errors in the Errors collection of the ActiveConnection.

This is followed by the actual error message returned by Microsoft SQL Server:

Cannot insert the value NULL into column 'CustomerID', table 'NorthwindCS.dbo.Customers'; column does not allow nulls.

Additional query words: pra

Keywords: kbbug kbnofix KB202868

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.