How To Create Hierarchical Recordsets Programmatically (196029)

SUMMARY

When you create hierarchical recordsets programmatically, you do not have to specify a data provider. However, a Shape provider is still needed to provide a session context. In this case, the shape command would not contain an actual SQL statement to append columns to (that is, Select SQL syntax). Child commands would be appended as columns to the higher level recordset without any source table. This yields the following hierarchy:

   ParentRS

     |
     |_ _ _ _ _ ChildRS
                  |
                  |_ _ _ _ _ GrandChildRS
                                    |
                                    |_ _ _ _ _ etc..

MORE INFORMATION

The following sample code demonstrates how to create hierarchical recordsets programmatically by using Visual Basic (two nested levels as described in the "Summary" section).

Note

Using the correct Shape syntax is very important to build the right hierarchical relationship.

For a complete list of data types that could be used to create new fields within the Shape syntax, please refer to the documentation under Type Property (ADO).

Note2

This article was written when the current version of MDAC was 2.0. This code will not run without modification under MDAC 2.1 sp2. The code will run as is under versions 2.5 and later of MDAC. Please reference the following article.

240341 BUG: Creating Fabricated Hierarchical Recordset Fails w/ MDAC

Visual Basic Code

Open a new Visual Basic project and add a reference to the "Microsoft ActiveX Data Objects Library."
Add the "Microsoft Hierarchical FlexGrid Control 6.0," to the project's component list.
Change the default name of the Hierarchical FlexGrid Control to (MSH1).

Add a command button (Command1) to your form and place the following code in the general declaration of Form1:

      Private Sub Command1_Click()

         Dim rs As New ADODB.Recordset
         Dim rsCh As ADODB.Recordset
         Dim rsGrndCh As ADODB.Recordset

         rs.ActiveConnection = "provider=msdatashape;data provider=none;"

         rs.Open " SHAPE APPEND new adInteger As PID, " & _
               " New adVarChar(10) As StudentName, " & _
               "((SHAPE APPEND new adInteger As ChID, " & _
               " New adVarChar(10) As Course, " & _
               "((SHAPE APPEND new adInteger As GrndChID, " & _
               " New adBSTR As Description) RELATE " & _
               " ChID TO GrndChID) As GrandChild) RELATE PID TO ChID) " & _
               "AS Child" , , adOpenStatic, adLockOptimistic

          ' Add a sample record in the parent recordset
          rs.AddNew Array("PID", "StudentName"), Array(1, "Jim Smith")

          ' Now add a two sample child records related to the original
          ' parent's record
          Set rsCh = rs("Child").Value
          For i = 0 To 1
             rsCh.AddNew Array("ChID", "Course"), Array(1, "Course #1" & i)

             ' Now add two sample Grand-child records for each child record
             Set rsGrndCh = rsCh("GrandChild").Value
             For j = 1 To 2
                rsGrndCh.AddNew Array("GrndChID", "Description"), _
                      Array(i, "Description" & Str(j))
             Next
          Next

          Set MSH1.DataSource = rs

          MsgBox "Successfully Done..."

          rs.Close
          rsCh.Close
          rsGrndCh.Close

      End Sub

Press the F5 key to run the project and see the three nested hierarchical recordsets in the MSHFlexGrid control.