Microsoft KB Archive/826758

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

Article ID: 826758

Article Last Modified on 4/5/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-





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

This article applies only to a Microsoft Access project (.adp).



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

This Recordset is not updatable



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

281998 How to bind Microsoft Access forms to ADO recordsets

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.  Start Access. Open the Access sample database project that is named NorthwindCS.adp. In the Database window, click Forms under the Objects section. In the right pane, right-click Customers, and then click Design View. On the View menu, click Properties. In the properties of the form, clear the Record Source property of the form to unbind the form.</li> Close the Properties dialog box.</li> On the View menu, click Code.</li>  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 = &quot;MSDAORA&quot; .Properties(&quot;Data Source&quot;).Value = &quot;<Oracle Data Source Name>&quot; .Properties(&quot;User ID&quot;).Value = &quot; &quot; .Properties(&quot;Password&quot;).Value = &quot; &quot; .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 = &quot;SELECT * FROM TableName&quot; .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 </li> Close the Visual Basic Editor.</li> On the File menu, click Save.</li> On the View menu, click Form View.</li> 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 &quot;Symptoms&quot; section.</li></ol>

Additional query words: acc2003 Access 2003 ACC2002 Access 2002 cant update ACC2007

Keywords: kbtshoot kbexpertiseinter kbcode kbprogramming kbado kbforms kberrmsg kbprb kboracle800 kbadp KB826758

-

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

© Microsoft Corporation. All rights reserved.