HOWTO: Create Hierarchical Recordsets Programmatically

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
• 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).

Note

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. Select F5 to run the project and see the three nested hierarchical recordsets in the MSHFlexGrid control.

Additional query words:

Keywords : kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:2.0,2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbhowto