ACC2000: Forms Based on ADO Recordsets Are Read-Only (227053)

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.

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:

    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.

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.

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:

   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.