INFO: Using Disconnected Hierarchical Recordsets

ID: Q213856

The information in this article applies to:


Hierarchical recordsets can be used as an alternative to JOIN and GROUP BY syntax when you need to access parent-child and summary data. These recordsets can be created by using the SHAPE provider, MSDataShape. Many applications now use a 3-tier architecture and might find it useful to create these recordsets, disconnect them from their data source, and then pass them back to the client. This article discusses what is necessary to pass disconnected hierarchical recordsets out-of-process.

This article assumes that you are already familiar with 3-tiered applications, hierarchical recordsets, and passing ADO recordsets as parameters to and from functions. For more information on these topics, please see the following articles on the Microsoft Knowledge Base:

Q189657 HOWTO: Use the ADO SHAPE Command

Q186342 HOWTO: Create a 3-Tier App using VB, MTS and SQL Server

Q182442 FILE: Adomts.exe Shows Using ADO w/ an MTS Component via DCOM

Working with recordsets based on SHAPE queries in 3-tier applications is similar to working with regular recordsets. However, there are some differences. Below are two of these differences that are specific to working with disconnect hierarchical recordsets:

  1. Recordsets based on parameterized SHAPE queries cannot be passed out-of-process. Here is an example of a parameterized SHAPE query:
       SHAPE {SELECT * FROM Employees WHERE LastName='Davolio'}
       APPEND ({SELECT * FROM Orders WHERE EmployeeID = ?} AS EmpOrders
          RELATE EmployeeID TO PARAMETER 0) 
    The reason these recordsets cannot be passed out-of-process is that parameterized SHAPE commands do not retrieve all the rows at creation-time. The child records are fetched on demand from the data source as the user moves through the parent records. If these recordsets were marshalled out-of-process there would not be enough information marshalled for the child records to be retrieved.

    The following MSDN article explains this in more detail:


  3. When sending hierarchical recordsets back to the server for updating, you need to manually loop through all of the child recordsets and issue an UpdateBatch for each one. While looping through the child recordsets, be sure to set the ActiveConnection property of each of the children recodsets to a valid connection object before issuing the UpdateBatch method. If you fail to set the ActiveConnection to a valid connection before issuing the UpdateBatch, no updates are sent to the database.

    Below is an example of a function that can be used to update hierarchical recordsets on the server side. This function accepts a hierarchical recordset as an incoming parameter and uses recursive calls to loop through all of the child recordsets and send the updates to the server:

Steps to Reproduce Behavior

  1. Start Visual Basic.

  2. Choose the default project type.

  3. Set a project reference to each of the following:

    Microsoft ActiveX Data Objects 2.1 Library


    Microsoft ActiveX Data Objects 2.0 Library

  4. Place a CommandButton on the From.

  5. Paste the following code into the Code Window:

    Dim m_cn As ADODB.Connection
    Private Sub Command1_Click()
            Dim cnn As New ADODB.Connection
            Dim rst As New ADODB.Recordset
            Dim rsChapter As Recordset
            cnn.CursorLocation = adUseClient
            cnn.Open "Provider=MSDataShape;Data Provider=sqloledb;" _
             & "Server=<Your Server Name>;uid=<UID>sa;pwd=<PWD>;Database=pubs;"
            rst.Open "SHAPE  {select * from authors}APPEND  
            ({select * from titleauthor} AS chapter RELATE au_id TO au_id)", _ 
               cnn, adOpenStatic, adLockBatchOptimistic
            Set rst.ActiveConnection = Nothing
            rst("au_lname") = "Smith"
            If (UpdateBatchX_ALLChildren("Provider=MSDataShape; " _ 
               & "Data Provider=sqloledb;Server=WUTANG;" _ 
               & "uid=sa;pwd=;Database=pubs;", rst) <> 0) Then _
                Debug.Print Err.Number & " -- " & Err.Description
                Exit Sub
            End If
    End Sub
    Public Function UpdateBatchX_ALLChildren(ByVal szConnect As String,  _ 
         ByVal rs As ADODB.Recordset) As Long
        'On Error GoTo errhandler
        Dim rsChild As ADODB.Recordset
        Dim i As Integer
        'Connect to the database .. m_cn is a module level ADODB connection
        if Not (szConnect = "") Then
            Set m_cn = CreateObject("ADODB.Connection")
            With m_cn
                .ConnectionString = szConnect
                .ConnectionTimeout = 15
                .CursorLocation = adUseServer
            End With
        End If
        'Update each of the child recordsets ... call recursively to
        'handle all of the children
        With rs
            .ActiveConnection = m_cn
            .UpdateBatch adAffectAllChapters
            For i = 0 To rs.Fields.Count - 1
                If (rs.Fields(i).Type = adChapter) Then
                    Set rsChild = New ADODB.Recordset
                    Set rsChild = rs.Fields(i).Value
                    UpdateBatchX_ALLChildren "", rsChild
                    Set rsChild = Nothing
                End If
            Next i
        End With
        'Only close the connection on the original call
        If Not (szConnect = "") Then
            Set m_cn = Nothing
        End If
        UpdateBatchX_ALLChildren = 0
        Exit Function
        UpdateBatchX_ALLChildren = Err.Number
        Err.Raise Err.Number, " UpdateBatchX ", Err.Description
    End Function

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Mike Ruthruff, Microsoft Corporation


For more information, please see the following articles in the Microsoft Knowledge Base:

Q191744 HOWTO: Extract Child Recordsets from Shaped Recordset

Q196968 PRB: SHAPE Provider Pulls Down All Records in Child Table

Q185423 PRB: Most ADO Recordset Properties Are Not Marshalled

Additional query words:

Keywords          : kbADO200 kbADO201 kbDCOM kbGrpVBDB kbGrpMDAC 
Version           : WINDOWS:2.0,2.1
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: May 3, 1999