Microsoft KB Archive/196029

= How To Create Hierarchical Recordsets Programmatically =

Article ID: 196029

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q196029





For a Microsoft Visual Basic .NET version of this article, see 316260.



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.</li></ol>

Keywords: kbhowto kbdatabase KB196029

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.