You cannot update a form that is bound to an ADO Oracle recordset in an Access project in Microsoft Access

When you try to programmatically edit records in an Oracle table by using an ActiveX Data Objects (ADO) recordset in a Microsoft Office Access database project, you may not be able to edit the records. Additionally, you may receive the following error message:

In Microsoft Access, you can bind an Access form to an ADO recordset that is using data from an Oracle database. If you want to update the form, you must make sure that both the following requirements are met:

• The ADO connection that is used by the recordset must use Microsoft OLE DB Provider for Oracle.
• The ADO recordset must be opened as a client-side cursor.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: When you fulfill the specified requirements in the Access database (.mdb or .accdb), you can update the form that is bound to the Oracle table through the ADO connection. However, when you perform the same tasks in an Access database project (.adp), you may not be able to update the Oracle data by using the form.

Steps to reproduce the behavior in Access 2003 and in earlier versions of Access

Note These steps assume that the Oracle database contains a table that is named CUSTOMERS. This table must have the same structure as the Customers table in the NorthwindCS database.

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.

1. Start Access.
2. Open the Access sample database project that is named NorthwindCS.adp.
3. In the Database window, click Forms under the Objects section.
4. In the right pane, right-click Customers, and then click Design View.
5. On the View menu, click Properties.
6. In the properties of the form, clear the Record Source property of the form to unbind the form.
7. Close the Properties dialog box.
8. On the View menu, click Code.
9. In the Visual Basic Editor, append the following code.
   
   Note The Visual Basic Editor may already contain some code. Do not remove any existing code. Append the following code to any existing code.
   
   Note The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run correctly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later). To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

   Private Sub Form_Open(Cancel As Integer)
   	
   	Dim cn As ADODB.Connection
   	Dim rs As ADODB.Recordset
   
   	'Create a new ADO Connection object.
   
   	Set cn = New ADODB.Connection
   
   	With cn
   		.Provider = "MSDAORA"
   		.Properties("Data Source").Value = "<Oracle Data Source Name>"
   		.Properties("User ID").Value = "<username>"     
   		.Properties("Password").Value = "<password>"
   		.Open
   	End With
   
   	'Create an instance of the ADO Recordset class and
   	'set its properties.
   	
   	Set rs = New ADODB.Recordset
   	
   	With rs
   		Set .ActiveConnection = cn     
   		.Source = "SELECT * FROM TableName"   
   		.LockType = adLockOptimistic
   		.CursorType = adOpenKeyset      
   		.CursorLocation = adUseClient
   		.Open	
   	End With   
   
   	'Set the Recordset property of the form to the ADO recordset.
   
   	Set Me.Recordset = rs    
   	Set rs = Nothing
   	Set cn = Nothing
   End Sub
   
   Private Sub Form_Unload(Cancel As Integer)
   
   	'Close the ADO connection that you opened.
   	
   	Dim cn As ADODB.Connection
   	Set cn = Me.Recordset.ActiveConnection
   	cn.Close
   	Set cn = Nothing
   End Sub
   

10. Close the Visual Basic Editor.
11. On the File menu, click Save.
12. On the View menu, click Form View.
13. In the Customers form, try to edit the records.
    
    Notice that you may not be able to edit the records. Additionally, you may receive the error message that is mentioned in the "Symptoms" section.