Chapter 14 -- Hierarchical Recordsets

[Previous] [Next]

Chapter 14

Data does not exist in a vacuum. Most of the data in your database is probably interconnected. Take a look at the referential integrity constraints in the Northwind database that ships with Microsoft Visual Basic, Microsoft Access, and Microsoft SQL Server. Most of the tables exist as either the parent or the child in a relationship.

In your applications, you're likely to encounter scenarios in which you'll want to retrieve data from two or more related tables in your database. With other object models such as Data Access Objects (DAO) and Remote Data Objects (RDO), you could retrieve such data in one of two ways: into separate result sets, or into one or more result sets using join operations.

As I alluded to in Chapter 10, recordsets based on join queries can be difficult to maintain and update. While the ADO Cursor Engine has features that can simplify these problems, most programmers avoid using join queries on tables that contain updatable data and one-to-many relationships, such as a Customers table and an Orders table. In addition to the problems with updating data, a join query results in a waste of memory; your application must store a single set of data in multiple memory locations. For example, every record of a Recordset object based on a join query between the Customers table and the Orders table contains a different order, but the customer information is duplicated for customers with multiple orders.

If you retrieve data into separate Recordset objects rather than performing a join, you'll need to write additional code to keep the Recordsets synchronized. For example, if you're viewing customer and order data, you'll want to apply a filter to the orders Recordset so that the only visible orders are those for the currently bookmarked customer.

To avoid these problems with joins and separate objects, the ADO development team introduced in version 2.0 the concept of a hierarchical Recordset. Based on a concept used in early versions of Microsoft FoxPro, a hierarchical Recordset is similar to many related Recordset objects. You can create a single query, such as the following, to retrieve data from multiple tables:

 strSQL = "SHAPE {SELECT * FROM Customers} AS Customers " & _ "APPEND ({SELECT * FROM Orders} AS Orders " & _ "RELATE CustomerID TO CustomerID) AS Orders" 

You can use this query in the following code to retrieve the data into a hierarchical Recordset object and access the different levels—also known as chapters—of the hierarchy:

 'Open the customers hierarchical Recordset object. rsCustomers.Open strSQL, cnNorthwind, adOpenStatic, _ adLockBatchOptimistic, adCmdText 'Reference the orders from the customers hierarchical Recordset object ' in the orders Recordset object. Set rsOrders = rsCustomers.Fields("Orders").Value 

Now as you move your cursor through the rsCustomers Recordset object, only the orders for the customer at the current cursor location are visible in the rsOrders Recordset object.

Let's first take a look at how to load the OLE DB provider that helps generate hierarchical Recordsets. Then we'll look at the two main types of hierarchies—standard and parameterized—as well as the hierarchical query syntax.