KB194516 - How To Use ADO Hierarchical Recordsets

Article ID: 194516

Article Last Modified on 7/13/2004

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

REFERENCES

For more information on the SHAPE syntax, please see the following article in the Microsoft Knowledge Base:

189657 How To Use the ADO SHAPE Command

Keywords: kbhowto kbdatabase KB194516