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