Mike Gunderloy provides some examples of ADO’s SHAPE_APPEND statement in action, including how to synchronize child and parent Recordsets. Mike also introduces the SHAPE_COMPUTE statement.
The SHAPE_APPEND command lets you work with hierarchical data (see my article, “Data Shaping: Handling Non-Relational Data,” in the December 1999 issue for the SHAPE_APPEND statement’s syntax). However, it’s hard to understand anything until you see some code. In this month’s article, I’m going to take a look at some examples of SHAPE statements. I’ll then introduce you to the SHAPE_COMPUTE statement.
You’ll find all of these examples in frmExample in the sample database available in the Download file (see also Figure 1). This form contains a set of command buttons, one for each example. When you click a button, the corresponding example is bound to a Hierarchical FlexGrid control on the form that lets you quickly see the Recordset returned by each example. The Recordsets on this form presume that you’ve got SQL Server or MSDE installed locally; if necessary, you can change the connection string in the form’s Load procedure to point to a different data source.
Figure 1
Single-level relation hierarchy
A single-level relation hierarchy relates two Recordsets, in this case Recordsets based on the Customers and Orders tables:
|
1 2 3 4 5 |
SHAPE {SELECT * FROM Customers} APPEND ({SELECT * FROM Orders} RELATE CustomerID TO CustomerID) |
Multiple-level relation hierarchy
The next step up in complexity is to nest two SHAPE_APPEND commands to create a Recordset based on the Customers, Orders, and Order Details tables:
|
1 2 3 4 5 6 7 8 9 10 11 |
SHAPE {SELECT * FROM Customers} APPEND ((SHAPE {SELECT * FROM Orders} APPEND ({SELECT * FROM [Order Details]} AS rstOrderDetails RELATE OrderID TO OrderID)) RELATE CustomerID TO CustomerID) |
Parameterized hierarchy
There’s no difference between the Recordset retrieved by a parameterized hierarchy and that retrieved by the equivalent relation hierarchy. Here’s the parameterized equivalent of the first, single-level example:
|
1 2 3 4 5 6 7 |
SHAPE {SELECT * FROM Customers} APPEND ({SELECT * FROM Orders WHERE CustomerID = ?} RELATE CustomerID TO PARAMETER 0) |
Although this Recordset will initially open faster than the equivalent relation hierarchy, moving from record to record is slower. As a result, you won’t see a performance difference between the two on the sample form. That’s because the Hierarchical FlexGrid control moves through all of the rows in the Recordset to populate itself.
Multiple relation hierarchy
By using more than one clause in the APPEND part of the SHAPE statement, you can create a Recordset with more than one chapter field, and thus more than one child Recordset:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SHAPE {SELECT * FROM Customers} APPEND({SELECT * FROM Orders WHERE ShippedDate > '1/1/97'} RELATE CustomerID TO CustomerID) as rstNewOrders, ({SELECT * FROM Orders WHERE ShippedDate <= '1/1/97'} RELATE CustomerID TO CustomerID) as rstOldOrders |
If you scroll through the resulting Recordset, you’ll see that each parent record is associated with two distinct child Recordsets, one each for new and old orders.
Relation hierarchy with aggregate
You can also choose to include aggregate columns within a relation hierarchy:
|
1 2 3 4 5 6 7 |
SHAPE {SELECT * FROM Customers} APPEND ({SELECT * FROM Orders} RELATE CustomerID TO CustomerID), MIN(Chapter1.ShippedDate) AS FirstShip |
This creates a Recordset with Customer and Order information, plus an additional aggregate column that contains the minimum value from any record in the ShippedDate column for each customer.
SHAPE_COMPUTE
Now that you’re familiar with the SHAPE_APPEND command, you’re ready to look at SHAPE_COMPUTE. With SHAPE_COMPUTE, you don’t supply a parent Recordset. Instead, the parent Recordset is created from the data in the child Recordset.
The general syntax of the SHAPE_COMPUTE statement is:
|
1 2 3 4 5 |
SHAPE {child_command} AS child_alias COMPUTE child_alias, aggregate_field_list BY group_field_list |
The curly braces around the child_command are required. The child_command can be one of four things:
- A SQL statement that returns a child Recordset.
- The name of a previously constructedshaped Recordset.
- Another SHAPE statement (so these commands can be nested).
- The TABLE keyword followed by the name of a table.
With SHAPE_COMPUTE, you must supply an alias for the child command after the AS keyword. This alias must be repeated as one of the columns listed in the COMPUTE clause. This alias defines the relation between the child Recordset and the implied parent Recordset.
The group_field_list is optional. If you supply a list of columns here, the parent Recordset is constructed so that each row has unique values in those columns, and the child Recordset is filtered to match. Any columns you list here will become columns in the parent Recordset. With this option, a record is generated in the parent Recordset for each unique combination of the fields that you list. The related records form the child Recordset for this parent record. If you don’t choose to supply a group_field_list, there will be only one row in the parent Recordset, and any aggregates it contains will refer to the entire child Recordset.
The aggregate_field_list is also optional. If you supply a list here, it must be composed of aggregate functions performed on fields in the child Recordset. Each entry in this list defines a column in the generated parent Recordset. Effectively, each row in the parent Recordset will represent a summary result generated from its child Recordset.
Aggregate functions
An aggregate function performs some calculation across all rows of a child (or other descendant) Recordset. These aggregate functions all accept fully qualified names for columns. A fully qualified name is simply one that specifies the entire path to a column. For example, if you have a hierarchical Recordset in which the top level contains Customers information plus a chapter named Orders, which in turn is a Recordset that contains a chapter named OrderDetails, which in turn contains a column named Quantity, the fully qualified name of this column would be:
|
1 |
Customers.Orders.OrderDetails.Quantity |
Table 1 shows the aggregate functions that are available in the SHAPE syntax.
Table 1. Aggregate functions supported in SHAPE.
| Function | Description |
| SUM(column) | Calculates the sum of all values in the specified column. |
| AVG(column) | Calculates the average of all values in the specified column. |
| MAX(column) | Retrieves the maximum value from the column. |
| MIN(column) | Retrieves the minimum value from the column. |
| COUNT(chapter) or COUNT(column) | Counts the number of rows in the chapter or in the column. |
| STDEV(column) | Calculates the standard deviation of the column. |
| ANY(column) | Picks a value from the column. It appears that this generally returns the first value, in cases where the column isn’t uniform. However, this behavior is not documented and therefore is not guaranteed. |
Calculated expressions
A calculated column can use an arbitrary expression to produce a result, but it can operate only on values in the row of the Recordset containing the CALC expression. CALC understands a variety of Visual Basic for Applications (VBA) functions. I’ve listed them in Table 2.
So what does that all mean? Here’s an example of a grouping hierarchy that shows detail and aggregated information. The SHAPE_COMPUTE statement creates the parent Recordset from the child Recordset:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SHAPE {SELECT Customers.CustomerID AS CustID, Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID} AS rstOrders COMPUTE rstOrders BY CustID, CompanyName |
You’ll see if you run this example that the statement creates a parent Recordset that contains the CustID and CompanyName fields. The parent Recordset has one record for each unique combination of CustID and CompanyName in rstOrders. The child Recordset for each parent consists of the records with matching CustID and CompanyNames (the CustID and CompanyName fields are both repeated in the child Recordset).
Table 2. VBA functions available to CALC.
| Type of Function | Function |
| Conversion | Asc, CBool, CByte, CCur[-TE], CDate, CDbl, CInt, CLng, CSng, CStr, CVar, CVDate, CVErr, Format, Format$, Hex, Hex$, Oct, Oct$, Val |
| Date and Time | Date, Date$, DateAdd, DateDiff, DatePart, DateSerial, DateValue, Day, Hour, Minute, Month, Now, Second, Time, Time$, Timer, TimeSerial, TimeValue, Weekday, Year |
| Financial | DDB, FV, IPmt, IRR, MIRR, NPer, NPV, Pmt, PPmt, PV, Rate, SLN, SYD |
| Mathematical | Abs, Atn, Cos, Exp, Fix, Int, Log, Rnd, Sgn, Sin, Sqr, Tan |
| Miscellaneous | Error, Error$, IIF, IsDate, IsEmpty, IsError, IsNull, IsNumeric, IsObject, QBColor, RGB, TypeName, VarType |
| String | Chr, ChrB, ChrW, Chr$, ChrB$, InStr$, LCase, LCase$, Left, LeftB, Left$, LeftB$, Len, LTrim, LTrim$, Mid, Mid$, Right, RightB, Right$, RightB$, RTrim, RTrim$, Space, Space$, Str, Str$, StrComp, StrConv, String, String$, Trim, Trim$, UCase, UCase$ |
Synchronizing Recordsets
When you’re moving through a Recordset that contains a chapter field, you can control whether a child Recordset based on this field remains synchronized to the parent Recordset by setting the parent Recordset’s StayInSync property appropriately. If you set this property to False, then moving the cursor in the parent Recordset will have no effect on the child Recordset and the child remains pointing to the original chapter. If you set the StayInSync property to True, the child Recordset will be re-fetched whenever you move the cursor in the parent Recordset.
For a demonstration of this property, take a look at frmSync in the sample database. This form simply fetches the same Recordset twice, once synchronized and once unsynchronized, and dumps results to the Immediate Window. The code begins by creating a Recordset using SHAPE_APPEND, and then retrieves the child Recordset from the field that it’s stored in:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
rstP.StayInSync = False rstP.Open "SHAPE {" & _ "SELECT CustomerID, CompanyName " & _ "FROM Customers} APPEND ({SELECT " & _ "CustomerID, OrderDate " & _ "FROM Orders} RELATE CustomerID " & _ "TO CustomerID)", cnn Set rstC = rstP.Fields("Chapter1").Value |
Now that I have two Recordsets, I can use the standard Recordset commands to move through the parent’s records and display a field from the parent and the child:
|
1 2 3 4 5 6 7 8 9 10 11 |
Debug.Print "Parent:" & rstP.Fields("CustomerID") Debug.Print "Child:" & rstC.Fields("CustomerID") Debug.Print "Executing MoveNext" rstP.MoveNext Debug.Print "Parent:" & rstP.Fields("CustomerID") Debug.Print "Child:" & rstC.Fields("CustomerID") |
The results are shown below. As you can see, while the code moves to the next record in the parent Recordset, the child Recordset is unaffected and remains on the first record in the first Chapter:
|
1 2 3 4 5 6 7 8 9 |
Parent:ALFKI Child:ALFKI Executing MoveNext Parent:ANATR Child:ALFKI |
However, if I set the StayInSync property to True, like this, I get different results:
|
1 |
rstP.StayInSync = True |
Now, when I move to the next record in the parent Recordset, the child Recordset is automatically set to the corresponding Chapter:
|
1 2 3 4 5 6 7 8 9 |
Parent:ALFKI Child:ALFKI Executing MoveNext Parent:ANATR Child:ANATR |
A warning: When you’re fetching a child Recordset, you must use the Value property of the chapter field in the parent Recordset, as shown in the preceding code sample. If you try to omit this property, you’ll get a type mismatch error, even though Value is the default property.
As you can see, the SHAPE syntax gives you a powerful new way to link Recordsets together. Wherever you have a one-to-many relationship in your data, SHAPE_APPEND (and its sibling SHAPE_COMPUTE) can let you represent data in its natural format. The result can be simpler, easier to maintain, and (as a result) more bug-free code.
(Excerpted from Mike Gunderloy’s Visual Basic Developer’s Guide to ADO by permission of Sybex, Inc., ISBN 0-7821-2556-5. Copyright © 1999, Sybex, Inc. , 1151 Marina Village Parkway, Alameda, CA 94501. All rights reserved. For further information please contact 1-800-227-2346. Additional information may be obtained on the Sybex Web Site (http://www.sybex.com). No part of this article may be reproduced in any way without the prior written agreement and permission of Sybex, Inc.) Note: GR-FX Pty Limited purchased the rights to publish Smart Access articles from Eli Journals in 2009.
Sidebar: Foundation Concepts: Consumers, Providers, and Service Providers
OLE DB, on which ADO rests, provides a set of objects that allow you to work with data. OLE DB divides these objects into three groups: consumers, providers, and service providers. Consumers are objects or applications that use data provided by the other two kinds of objects. A provider is an object that can extract data from a data source and return it in the standard OLE DB format. A service provider can’t extract data, but, like a consumer, it can accept data from a provider. A service provider, unlike a straight consumer, also manipulates the data and can pass it on to a consumer or another service provider. Since a service provider can work with any ADO provider, service providers can be very flexible. Microsoft’s search engine, Index Server, has a data provider that you can use to extract data from the search engine’s catalog (for example “Find me all of the documents containing the words Smart Access'”). Since the Index Server data provider returns data in the standard ADO format, you can use the Data Shaping server to manipulate that data. All of these features are available from any development tool that can work with objects, including Access 97 and Access 2000.
Your download file is called 001GUNDERLOY.ZIP in the folder 2000-01
