HOWTO: Manufacture an ADO Recordset Based on Another Recordset

ID: Q247868


The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5
  • Microsoft Visual Basic Professional and Enterprise Editions, 32-bit only, for Windows, versions 5.0, 6.0


SUMMARY

Some corporate policies allow read-only access to database tables with all updates being performed through stored procedures. If you want to bind a data control, such as the Microsoft Data Grid or List View control, to an ADO recordset, this can pose a problem keeping the recordset in sync with the data if you can allow adding, deleting, and modifying records but you cannot edit it directly through the recordset.


MORE INFORMATION

If you want to keep a recordset in sync with the server but you cannot add records through the hierarchical recordset, there are a number of workarounds:

  1. You can requery the recordset. This has the disadvantage of poor performance.


  2. You can disconnect the recordset and perform additions and other modifications in parallel with stored procedures. The problem here is that certain fields are read-only, such as Identity and TimeStamp, and you cannot set their value. This becomes problematic if the Identity column is supplied to the stored procedures to identify the record to be updated, or if the TimeStamp column is passed to the stored procedure to determine if someone else has edited the record in the meantime. There are a number of workarounds:


    1. You can copy the data to a local database, such as a Jet MDB, and use datatypes that do not make the field read-only. You can then update the recordset in parallel to using stored procedures and keep the data in sync.


    2. You can manufacture a recordset based on the original and copy the data into it. The fields are all updatable and you can update the recordset in parallel to using stored procedures.


This article provides sample code for the second workaround. It is generic enough to work with any non-hierarchical recordset (Chapter fields are not copied). If you need to use hierarchical recordsets, please refer to the following Microsoft Knowledge Base article:
Q241202 HOWTO: Produce a Manufactured Hierarchical Recordset Base on an Existing Recordset
This article provides the following functions:

Function Name Description
MakeRS Creates a closed, empty Recordset with fields with the same names and data types as the original. Chaptered fields are not copied.
OpenAndFillRS Opens the destination Recordset and copies data from the (already open) source Recordset. Data in Chaptered fields are not copied.

The sample application has the following steps:
  1. It opens a recordset.


  2. It calls MakeRS to create an equivalent recordset that is not based on any provider and hence does not have any read-only fields.


  3. It calls OpenAndFillRS to open the recordset and copy the data from the original recordset.


  4. It closes the original recordset.


  5. It binds the ADO Data Grid to the manufactured recordset.


NOTE: The code does not provide samples of updating the server table and making equivalent changes to the manufactured recordset. This is left as an exercise for the reader because the manufactured recordset is now in a state to make this possible.

Sample Application

  1. Open a new project in Microsoft Visual Basic 6.0. Form1 is created by default.


  2. From the Projects menu, choose Components, select the "Microsoft DataGrid Control 6.0 (OLEDB)" control, and place it on the form. Make it fairly large.


  3. From the Projects menu, choose References, select "Microsoft ActiveX Data Objects 2.1 Library."


  4. Add the following code to the form module:


  5. 
    Option Explicit
    
    Private Sub Command1_Click()
    Dim cn As ADODB.Connection, rsSource As ADODB.Recordset, rsDest As ADODB.Recordset
      Set cn = New ADODB.Connection
      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb"
      Set rsSource = cn.Execute("SELECT * FROM Customers")
      Set rsDest = MakeRS(rsSource)
      OpenAndFillRS rsSource, rsDest
      rsSource.Close
      cn.Close
      Set DataGrid1.DataSource = rsDest
    End Sub
    
    Function MakeRS(ByVal rsSource As ADODB.Recordset) As ADODB.Recordset
    Dim rsTemp As ADODB.Recordset, F As ADODB.Field
      Set rsTemp = New ADODB.Recordset
      For Each F In rsSource.Fields
        If F.Type <> adChapter Then
          rsTemp.Fields.Append F.Name, F.Type, F.DefinedSize, F.Attributes And adFldIsNullable
          With rsTemp(F.Name)
            .Precision = F.Precision
            .NumericScale = F.NumericScale
          End With
        End If
      Next F
      Set MakeRS = rsTemp
    End Function
    
    Sub OpenAndFillRS(ByVal rsSource As ADODB.Recordset, ByVal rsDest As ADODB.Recordset)
    Dim F As ADODB.Field
      If rsSource.State = adStateClosed Then Exit Sub<BR/>
      If rsSource.EOF And rsSource.BOF Then Exit Sub
      If rsSource.CursorType <> adOpenForwardOnly Then
        If Not rsSource.EOF And Not rsSource.BOF Then
          rsSource.MoveFirst
        End If
      End If
      rsDest.CursorLocation = adUseClient
      rsDest.Open
     
      Do While Not rsSource.EOF
        rsDest.AddNew
        For Each F In rsSource.Fields
          If F.Type <> adChapter Then rsDest(F.Name).Value = F.Value
        Next F
        rsDest.Update
        rsSource.MoveNext
      Loop
    End Sub 
  6. Add a CommandButton, modify the connect string in the Command1_Click procedure, and run the project. The customer's data is displayed in the grid after you click the CommandButton.


© Microsoft Corporation 2000, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation

Additional query words:

Keywords : kbADO kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO250
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,2.5,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: January 17, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.