Problems binding form to ADO recordset that is using Batch Optimistic Locking (288375)

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?

RESOLUTION

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 = "SELECT * FROM Customers"
      .LockType = adLockBatchOptimistic
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .Open
      Set .ActiveConnection = Nothing
   End With
   Set Me.Recordset = rs
End Sub

MORE INFORMATION

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.
Click to select the Microsoft ActiveX Data Objects 2.5 Library check box, and then click OK to close the References dialog box.

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 = "SELECT * FROM Customers"
      .LockType = adLockBatchOptimistic
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .Open
      Set .ActiveConnection = Nothing
   End With
   Set Me.Recordset = rs
End Sub

On the File menu, click Close and return to Microsoft Access.
Save the form, and then close it.
Open the form in Form view.
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.
Click OK to dismiss the error message.
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.
Close the form.

Note that you receive the following error message multiple times:

REFERENCES

For more information about binding Access forms to ADO recordsets, click the following article number to view the article in the Microsoft Knowledge Base:

281998 How to bind Microsoft Access forms to ADO recordsets

For more information about the difference between the Connection and the AccessConnection properties, click the following article number to view the article in the Microsoft Knowledge Base:

281784 Difference between Connection and AccessConnection properties