INFO: ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q185425

SUMMARY

This article describes how to use the SHAPE APPEND syntax to produce hierarchical recordsets and how to traverse them. Sample code is provided for VBA.

Back to the top

Hierarchical recordsets present an alternative to using JOIN syntax when accessing parent-child data. Hierarchical recordsets differ from a JOIN in that with a JOIN, both the parent table fields and child table fields are represented in the same recordset. With a hierarchical recordset, the recordset contains only fields from the parent table. In addition, the recordset contains an extra field that represents the related child data, which you can assign to a second recordset variable and traverse.

Hierarchical recordsets are made available via the MSDataShape provider, which is implemented by the client cursor engine.

A new clause, SHAPE, is provided to relate SELECT statements in a hierarchical fashion. The syntax is summarized below:

   SHAPE {parent-command} [[AS] name]
   APPEND ({child-command} [[AS] name] RELATE parent-field TO child-field)
   [,({child2-command} ...)]

Note

By default, the child recordsets in the parent recordset will be called Chapter1, Chapter2, etc., unless you use the optional [[AS] name] clause to name the child recordset.
You can nest the SHAPE command. The {parent-command} and/or {child-command} can contain another SHAPE statement.
The {parent-command} and {child-command} do not have to be SQL SELECT statements. They can use whatever syntax is supported by data provider.

The example below illustrates a hierarchical recordset using the publishers and titles tables in the SQL Server pubs database. The same code could easily be modified to use the Biblio Microsoft Access database that ships with Visual Studio. (You would need to change the SHAPE syntax and debug print for two fields: PUB_ID to PUBID and PUB_NAME to NAME.)

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures.

Back to the top

VBA Example

This step-by-step example is written for Visual Basic, but could be used in Microsoft Access just as easily.

Create a new VBA project and add a form named Form1 and a command button named Command1.
Add a reference to the Microsoft ActiveX Data Objects Library.

Add the following code to the form:

Note You must change User UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

      Private Sub Command1_Click()
        Dim cn As ADODB.Connection, rsPub As ADODB.Recordset, _
          rsTitle As ADODB.Recordset, SQL As String
        Set cn = New ADODB.Connection
        Set rsPub = New ADODB.Recordset
        cn.Provider = "MSDataShape"
        cn.Open "dsn=Pubs;uid=<username>;pwd=<strong password>;database=pubs"
        SQL = "SHAPE {SELECT * FROM publishers} APPEND " & _
              "({SELECT * FROM titles} AS PubTitles " & _
              "RELATE pub_id TO pub_id)"
        rsPub.Open SQL, cn, adOpenStatic, adLockReadOnly, adCmdText
        Do While Not rsPub.EOF
          Debug.Print "Publisher", rsPub!pub_name
          Set rsTitle = rsPub!PubTitles.Value
          Do While Not rsTitle.EOF
            Debug.Print , rsTitle!Title
            rsTitle.MoveNext
          Loop
          rsTitle.Close
          rsPub.MoveNext
        Loop
        rsPub.Close
        cn.Close
        Set rsTitle = Nothing
        Set rsPub = Nothing
        Set cn = Nothing
      End Sub

Run the form and click the command button. The list of publishers and titles will be displayed in the Debug/Immediate window.

Back to the top

REFERENCES

ADO Help; search on: "Shape Append Command"

For additional information on SHAPE syntax, click the article number below to view the article in the Microsoft Knowledge Base:

189657 (http://support.microsoft.com/kb/189657/EN-US/ ) HOWTO: Use the ADO SHAPE Command

Back to the top

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

Back to the top

kbdatabase kbinfo KB185425

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top