Microsoft KB Archive/194516

= How To Use ADO Hierarchical Recordsets =

Article ID: 194516

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q194516



SUMMARY
ActiveX Data Objects (ADO) Hierarchical recordsets greatly resemble parent- child relationships created using the SET RELATION command in native FoxPro language. Hierarchical recordsets are created using a series of SQL statements in conjunction with keywords that define the relationships between them. For example, the following command creates a relation hierarchy between a hypothetical PUBLISHERS and TITLES table that shows which title belongs to which publisher: SHAPE {SELECT * FROM PUBLISHERS} ; APPEND DETAIL {SELECT * FROM Titles} ; RELATE pub_id to pub_id AS oRSPUBTITLES



MORE INFORMATION
The following example creates a parent-child-grandchild hierarchy using the Microsoft Access sample NORTHWIND database. It assumes that a User DSN named NORTHWIND exists on the computer.

In order to use this example, you must have Microsoft Data Access Components (MDAC) version 2.x or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from:

http://msdn.microsoft.com/dataaccess

Sample code: * Demonstrate the use of Hierarchical recordsets * Uses the MSDataShape provider to create a hierarchical * recordset from the Northwind database's  * Customers, Orders and Order Details tables *  * It displays on the desktop the CustomerID and Company Name, * for each customer, the order ids, indented, and for * each order id, the product id from the order details table

oConnection = CREATEOBJECT("ADODB.Connection") oRecordSet = CREATEOBJECT("ADODB.Recordset")

WITH oConnection .Provider="MSDataShape" .OPEN("DSN=NORTHWIND") ENDWITH

oRecordSet.ActiveConnection = oConnection oRecordSet.OPEN ("SHAPE {SELECT * FROM Customers} AS Customers " + ;     "APPEND ((SHAPE {SELECT * FROM Orders} AS Orders " + ;      "APPEND ({SELECT * FROM [Order details]} AS Orderdetails " + ; "RELATE OrderID TO OrderID) As OrderDetails) " + ; "RELATE CustomerID TO CustomerID)")

* Loop through the CUSTOMERS table DO WHILE NOT oRecordSet.EOF * List the customer ID and Company Name ? oRecordSet.FIELDS("CustomerID").VALUE, ; oRecordSet.FIELDS("CompanyName").VALUE

* For each customer record, a field called Orders * is added. This Orders field is a Recordset oOrders=oRecordSet.FIELDS("Orders").VALUE DO WHILE NOT oOrders.EOF * For each Orders record, an OrderID field is        * added, which is a Recordset ? "   ", oOrders.FIELDS("OrderID").VALUE * OrderDetails is a Recordset object oOrderDetails = oOrders.FIELDS("OrderDetails").VALUE DO WHILE NOT oOrderDetails.EOF ? "       ", oOrderDetails.FIELDS("productid").VALUE oOrderDetails.MoveNext ENDDO oOrders.MoveNext ENDDO oRecordSet.MoveNext ENDDO

