ACC2000: Forms Based on ADO Recordsets Are Read-Only

ID: Q227053

The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.

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


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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

  1. Create an ODBC data source based on the Pubs sample database installed with Microsoft SQL Server.


  2. Open the sample database Northwind.mdb.


  3. Link the Authors table from the data source that you created in step 1.


  4. Create a new form based on the Authors table, and open it in Design view.


  5. Add all fields to the form's detail section.


  6. Clear the form's RecordSource property, so that the form is no longer directly bound to the Authors table.


  7. On the View menu, click Code.


  8. On the Tools menu, click References.


  9. Add a reference to the Microsoft ActiveX Data Objects 2.1 Library if it is not already selected.


  10. Click OK to close the References dialog box.


  11. Add the following code to the form's module:


    12. 
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
  12. Close the form, and save it as frmAuthors.


  13. Open the frmAuthors form in Form view. Note that the form is successfully populated with data from the ADO recordset.


  14. Try to edit any field on the form.

    Note that you can successfully edit the form.


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.

For additional information about ISAM data sources that you can link to a Microsoft Jet database, please see the following article in the Microsoft Knowledge Base:
Q180105 ACC2000: Supported File Types for Import, Export, and Linking
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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

  1. Open the sample database Northwind.mdb.


  2. Open the Products form in Design view.


  3. Clear the form's RecordSource property, so that the form is no longer directly bound to the Products table.


  4. On the View menu, click Code.


  5. On the Tools menu, click References.


  6. Add a reference to the Microsoft DAO 3.6 Object Library if it is not already selected.


  7. Click OK to close the References dialog box.


  8. Add the following code to the form's module:


    9. 
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
  9. Save and close the Products form.


  10. Open the Products form in Form view. Note that the form is successfully populated with data from the DAO recordset.


  11. Try to edit any field in the form.

    Note that you can successfully edit the form.



STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


MORE INFORMATION

Steps to Reproduce Behavior

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

  1. Open the sample database Northwind.mdb.


  2. Open the Products form in Design view.


  3. Clear the form's RecordSource property, so that the form is no longer directly bound to the Products table.


  4. On the View menu, click Code.


  5. On the Tools menu, click References.


  6. Add a reference to the Microsoft ActiveX Data Objects 2.1 Library if it is not already selected.


  7. Click OK to close the References dialog box.


  8. Add the following code to the form's module:


    9. 
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
  9. Save and close the Products form.


  10. Open the Products form in Form view.

    Note that the form is successfully populated with data from the ADO recordset.


  11. 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.



REFERENCES

For more information about the form Recordset property, click Microsoft Access Help on the Help menu, type "form recordset property" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: pra 


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: July 6, 1999