HOWTO: Create Hierarchical Recordsets Programmatically

ID: Q196029

The information in this article applies to:

ActiveX Data Objects (ADO), version 2.0
Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0

SUMMARY

When creating hierarchical recordsets programmatically, there is no need 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:

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

MORE INFORMATION

Below is some sample code that demonstrates the process of creating hierarchical recordsets programmatically using Visual Basic (two nested levels as described in the SUMMARY section above).

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

Visual Basic Code

1. Open a new Visual Basic project and add a reference to the "Microsoft

   ActiveX Data Objects 2.0 Library."

2. Add the "Microsoft Hierarchical FlexGrid Control 6.0," to the

   project's component list.

3. Change the default name of the Hierarchical FlexGrid Control to (MSH1).

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

5. Press F5 to run the project and see the three nested hierarchical

   recordsets in the MSHFlexGrid control.

Additional query words:

Keywords          : kbnokeyword
Version           : WINDOWS:2.0,6.0
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: December 17, 1998