Microsoft KB Archive/288375

= Problems binding form to ADO recordset that is using Batch Optimistic Locking =

Article ID: 288375

Article Last Modified on 3/7/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q288375



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 edit a record in a form that is bound to an ActiveX Data Objects (ADO) recordset, you receive the following error message:

This recordset is not updateable.

When you close the form, you receive the following error message multiple times:

You can't save this record at this time.

Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?



CAUSE
This problem occurs when all the following conditions are true:
 * Microsoft Data Access Components (MDAC) 2.5 is installed on your computer.
 * The ADO connection used by the recordset was opened using the Microsoft Access 10.0 OLE DB service provider and the Microsoft Jet OLE DB data provider.
 * The LockType property of the ADO recordset is set to the adLockBatchOptimistic constant.



Install MDAC 2.6 on the Computer
This problem has been corrected in MDAC 2.6. To resolve this problem, upgrade your computer to MDAC 2.6. You can obtain the latest version of MDAC from the following Microsoft Web site:

http://msdn.microsoft.com/dataaccess

To temporarily work around this problem, use the following method.

Change the ADO Connection to Only Use the Jet Provider
You can work around this problem by opening the ADO connection using only the Microsoft Jet 4.0 OLE DB data provider, and not the Microsoft Access 10.0 OLE DB service provider. If you are writing your code in the Microsoft Access user interface, set the ActiveConnection property of the ADO recordset to the CurrentProject.Connection property instead of to the CurrentProject.AccessConnection property. For example, the following code opens an ADO recordset using only the Microsoft Jet 4.0 OLE DB data provider: Private Sub Form_Open(Cancel As Integer) Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset With rs     Set .ActiveConnection = CurrentProject.Connection .Source = &quot;SELECT * FROM Customers&quot; .LockType = adLockBatchOptimistic .CursorType = adOpenKeyset .CursorLocation = adUseClient .Open Set .ActiveConnection = Nothing End With Set Me.Recordset = rs End Sub



STATUS
Microsoft has confirmed that this is a problem in Microsoft Access.



Steps to reproduce the behavior
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

 Start Microsoft Access on a computer that does not have MDAC 2.6 installed. Open the sample database Northwind.mdb. Open the Customers form in Design view. On the View menu, click Code. On the Tools menu, click References.</li> Click to select the Microsoft ActiveX Data Objects 2.5 Library check box, and then click OK to close the References dialog box.</li>  Type or paste the following code in the module of the form: Private Sub Form_Open(Cancel As Integer) Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset With rs     Set .ActiveConnection = CurrentProject.AccessConnection .Source = &quot;SELECT * FROM Customers&quot; .LockType = adLockBatchOptimistic .CursorType = adOpenKeyset .CursorLocation = adUseClient .Open Set .ActiveConnection = Nothing End With Set Me.Recordset = rs End Sub </li> On the File menu, click Close and return to Microsoft Access.</li> Save the form, and then close it.</li> Open the form in Form view.</li> Modify the value in the CompanyName field, and then use the TAB key to move to the next field in the form. Note that you receive the following error message:

This recordset is not updateable.

</li> Click OK to dismiss the error message.</li> On the Edit menu, click Undo Typing.

This restores the original value in the CompanyName field. However, the record is still in an edited state.</li> Close the form.</li></ol>

Note that you receive the following error message multiple times:

You can't save this record at this time.

Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?

<div class="references_section">