Microsoft KB Archive/227053

= ACC2000: Forms Based on ADO Recordsets Are Read-Only =

Article ID: 227053

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q227053





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

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2002 version of this article, see 281998.



SYMPTOMS
When you try to edit data in a form based on an ActiveX Data Objects (ADO) recordset, the form is read-only. This occurs even if you can successfully edit the recordset directly with ADO.



CAUSE
Microsoft Access forms will only allow you to edit data from an ADO recordset if the ADO recordset is created by using a combination of the MSDataShape and SQL Server OLEDB providers.



RESOLUTION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The resolution depends on the source of data of the recordset that you want to bind to the form's Recordset property.

Data Source Based on Microsoft SQL Server
If the source of the data for the form is provided by Microsoft SQL Server 6.5 or later, you can use the MSDataShape and SQL Server OLEDB providers to create an ADO recordset that can be edited in a Microsoft Access form.

NOTE: Microsoft Access forms will only allow you to edit one table from the recordset on which the form is based. If you set the form's Recordset property to an ADO recordset based on a view, stored procedure, or SQL statement that contains multiple tables, you must set the form's UniqueTable property to the name of the table that you want to edit in the form. Fields from other tables in the form's recordset will be visible on the form, but you will not be able to edit them.

To use the MSDataShape and SQL Server OLEDB providers to create an ADO recordset and to assign the recordset to a form, follow these steps:

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.

 Create an ODBC data source based on the Pubs sample database installed with Microsoft SQL Server. Open the sample database Northwind.mdb. Link the Authors table from the data source that you created in step 1. Create a new form based on the Authors table, and open it in Design view. Add all fields to the form's detail section.</li> Clear the form's RecordSource property, so that the form is no longer directly bound to the Authors table.</li> On the View menu, click Code.</li> On the Tools menu, click References.</li> Add a reference to the Microsoft ActiveX Data Objects 2.1 Library if it is not already selected.</li> Click OK to close the References dialog box.</li>  Add the following code to the form's module: Sub Form_Open(Cancel As Integer) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection With cn     .Provider = "MSDataShape" 'The code below uses a sample server name, user ID, and password. 'Be sure to use your actual server name, user ID, and password. .ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA _        SOURCE=MySQLServerName;DATABASE=Pubs;UID=sa;PWD=;" .CursorLocation = adUseServer .Open End With Set rs = New ADODB.Recordset With rs     .Source = "SELECT * FROM Authors" .ActiveConnection = cn     .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open End With Set Me.Recordset = rs  Me.UniqueTable = "Authors" End Sub </li> Close the form, and save it as frmAuthors.</li> Open the frmAuthors form in Form view. Note that the form is successfully populated with data from the ADO recordset.</li> Try to edit any field on the form.

Note that you can successfully edit the form.</li></ol>

Data Source Based on Other Data Sources
If the source of data for the form is provided by any other source, such as the Microsoft Jet database engine, you cannot create an ADO recordset that can be edited with a form, even if you can edit the recordset directly with ADO. The only solution in this case is to use Data Access Objects (DAO) to create the recordset, and then to assign the recordset to the form's Recordset property. DAO is highly optimized for the Microsoft Jet database engine, and can access a number of ISAM or ODBC data sources that are accessible by the Jet database engine.

If you are using an ISAM or ODBC data source, link the table to a Microsoft Jet database (.MDB) and use DAO to open a recordset based on the linked table. As long as the recordset can be edited directly via DAO, a form based on the recordset can be edited as well. To create a DAO recordset that can be edited in a Microsoft Access form, follow these steps.

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.

<ol> Open the sample database Northwind.mdb.</li> Open the Products form in Design view.</li> Clear the form's RecordSource property, so that the form is no longer directly bound to the Products table.</li> On the View menu, click Code.</li> On the Tools menu, click References.</li> Add a reference to the Microsoft DAO 3.6 Object Library if it is not already selected.</li> Click OK to close the References dialog box.</li> <li> Add the following code to the form's module: Sub Form_Open(Cancel As Integer) Dim db As DAO.Database Dim rs As DAO.Recordset

'Assumes the linked table is in the current database Set db = CurrentDb Set rs = db.OpenRecordset("SELECT * FROM Products", dbOpenDynaset) Set Me.Recordset = rs     End Sub </li> <li>Save and close the Products form.</li> <li>Open the Products form in Form view. Note that the form is successfully populated with data from the DAO recordset.</li> <li>Try to edit any field in the form.

Note that you can successfully edit the form.</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

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

<ol> <li>Open the sample database Northwind.mdb.</li> <li>Open the Products form in Design view.</li> <li>Clear the form's RecordSource property, so that the form is no longer directly bound to the Products table.</li> <li>On the View menu, click Code.</li> <li>On the Tools menu, click References.</li> <li>Add a reference to the Microsoft ActiveX Data Objects 2.1 Library if it is not already selected.</li> <li>Click OK to close the References dialog box.</li> <li> Add the following code to the form's module: Sub Form_Open(Cancel As Integer) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset

'Use Microsoft Access's OLEDB connection to the Jet database Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset With rs     .Source = "SELECT * FROM Products" .ActiveConnection = cn     .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open End With Set Me.Recordset = rs     End Sub </li> <li>Save and close the Products form.</li> <li>Open the Products form in Form view.

Note that the form is successfully populated with data from the ADO recordset.</li> <li>Try to edit any field in the form.

Note that you are unable to edit the form and that Microsoft Access displays the text "Form is read-only" in the status bar.</li></ol>

<div class="references_section">