INFO: ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java
ID: Q185425
|
The information in this article applies to:
-
ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
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.
MORE INFORMATION
Hierarchical recordsets present an alternative to using JOIN syntax when
accessing parent-child data. Hierachical 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.
Hierachical 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.
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:
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=sa;pwd=;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.
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:
Q189657 HOWTO: Use the ADO SHAPE Command
© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation
Additional query words:
OffCon epucon
Keywords : kbADO kbADO200 kbDatabase kbJava kbSQLServ KbVBA kbVC500 kbGrpVBDB kbGrpMDAC kbDSupport kbVB500 kbADO210sp2
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbinfo