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

This article was previously published under Q185425
This article has been archived. It is offered "as is" and will no longer be updated.
This article describes how to use the SHAPE APPEND syntax to producehierarchical recordsets and how to traverse them. Sample code is providedfor VBA.
More information
Hierarchical recordsets present an alternative to using JOIN syntax whenaccessing parent-child data. Hierarchical recordsets differ from a JOIN inthat with a JOIN, both the parent table fields and child table fields arerepresented in the same recordset. With a hierarchical recordset, therecordset contains only fields from the parent table. In addition, therecordset 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 ahierarchical fashion. The syntax is summarized below:
   SHAPE {parent-command} [[AS] name]   APPEND ({child-command} [[AS] name] RELATE parent-field TO child-field)   [,({child2-command} ...)]				
  • By default, the child recordsets in theparent 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 publishersand titles tables in the SQL Server pubs database. The same code couldeasily be modified to use the Biblio Microsoft Access database that shipswith Visual Studio. (You would need to change the SHAPE syntax and debugprint for two fields: PUB_ID to PUBID and PUB_NAME to NAME.)

Microsoft provides programming examples for illustration only, withoutwarranty either expressed or implied, including, but not limited to, theimplied warranties of merchantability and/or fitness for a particularpurpose. This article assumes that you are familiar with the programminglanguage being demonstrated and the tools used to create and debugprocedures.

VBA Example

This step-by-step example is written for Visual Basic, but could be used inMicrosoft Access just as easily.
  1. Create a new VBA project and add a form namedForm1 and a command button named Command1.
  2. Add a reference to the Microsoft ActiveX Data Objects Library.
  3. 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					
  4. Run the form and click the command button. The list of publishers and titles will be displayed in the Debug/Immediate window.
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 HOWTO: Use the ADO SHAPE Command
OffCon epucon

Article ID: 185425 - Last Review: 10/26/2013 01:21:00 - Revision: 3.0

Microsoft ActiveX Data Objects 2.7

  • kbnosurvey kbarchive kbdatabase kbinfo KB185425