HOWTO: Use ADO Hierarchical Recordsets

• Microsoft Visual FoxPro for Windows, version 6.0
• Microsoft Data Access Components version 2.1 SP2

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://www.microsoft.com/data/

   * 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:

Q189657 HOWTO: Use the ADO SHAPE Command

Additional query words:

Keywords : kbActiveX kbDatabase kbVFp600 kbGrpFox kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbhowto