HOWTO: Re-Shape a Hierarchical Recordset

ID: Q249097

The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5

SUMMARY

MDAC 2.1 introduces re-shaping as a feature of the MSDataShape provider. This article discusses how re-shaping can benefit an application and contains samples to illustrate the concepts.

MORE INFORMATION

Re-shaping is a new feature of the MSDataShape provider in MDAC 2.1. It has the following advantages:

You can see all child records regardless of parent.
You can perform calculations on an existing recordset without having to re-select the data from the server.
You can shape another Recordset as parent to an existing cached Recordset.

The following Microsoft Knowledge Base article illustrates how to use re-shaping in a parameterized query to select child data for those Data Providers that do not support parameterizing child statements:

Q249027 HOWTO: Call a Parameterized Child Command via the MSDataShape Provider

Re-shaping has the following limitations:

You cannot re-shape a parameterized SHAPE statement because the Child data is not cached locally but is dynamically selected from the Data Provider for the current parent record.

NOTE: A parameterized SHAPE statement is different than a parameterized query. See the following Microsoft Knowledge Base article for more information:
Q189657 HOWTO: Use the ADO SHAPE Command
The re-shaping process does not add fields to the recordset (that is, you cannot add child recordsets underneath the re-shaped recordset). The re-shaped recordset retains any children it might have had originally.

A recordset must already be open on the cached data before you can re-shape it. If no recordsets are open on it, the data is flushed from the cache and is no longer available.

Seeing All the Child Records

When using the Batch Optimistic cursor, updated records are flagged. If you need to perform an operation on all updated child records, you would normally have to get to them through the parent record. With re-shaping, you can open a new recordset that shows all child records and you can then use the Filter method to see only the records due to be updated.

The following sample code consists of three parts: the first opens the hierarchical recordset with Employees as the parent recordset (rs) and Orders as the child (rsChild). The second updates the first Order for each Employee. The third re-shapes the cached Orders information and filters it in order to print out all records that have pending updates:


Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsChild As ADODB.Recordset, rs2 As ADODB.Recordset
  Set cn = New ADODB.Connection
  cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
  Set rs = New ADODB.Recordset
  rs.CursorLocation = adUseClient
  rs.Open "SHAPE {SELECT * FROM Employees} AS Employees APPEND ({SELECT * FROM Orders} AS Orders RELATE EmployeeID TO EmployeeID)", cn, adOpenStatic, adLockBatchOptimistic, adCmdText
'
' Edit the first Order for each Employee
'
  Do While Not rs.EOF
    Set rsChild = rs!Orders.Value
    If Not rsChild.EOF Then
    ' edit first record
      rsChild!Freight.Value = rsChild!Freight.Value * 1.1 ' increase freight by 10%
      rsChild.Update
    End If
    rs.MoveNext
  Loop
'
' To see all updated records
'
  Set rsChild = New ADODB.Recordset
  rsChild.Open "SHAPE Orders", cn, adOpenStatic, adLockBatchOptimistic, adCmdText
  rsChild.Filter = adFilterPendingRecords
  Do While Not rsChild.EOF
    Debug.Print rsChild!OrderID, rsChild!Employeeid, rsChild!CustomerID
    rsChild.MoveNext
  Loop
  Set rsChild = Nothing
  rs.CancelBatch         ' sample doesn't update data

Performing Calculations

You can use re-shaping to perform various computed rollups on an existing recordset without having to re-read the data from the server.

Building on the first sample, the following code reshapes the cached Orders information and computes and prints the total Freight for each Employee:


  Set rsChild = New ADODB.Recordset
  rsChild.Open "SHAPE Orders COMPUTE Orders, Sum(Orders.Freight) AS TotFreight, Any(Orders.EmployeeID) AS EmployeeID BY EmployeeID", cn, adOpenStatic, adLockBatchOptimistic, adCmdText
  Do While Not rsChild.EOF
    Debug.Print rsChild!Employeeid, rsChild!TotFreight
    rsChild.MoveNext
  Loop
  Set rsChild = Nothing

Adding a New Parent

If you need two hierarchical recordsets, for example Employees/Sales and Customers/Sales, the Sales table is read to the client twice. By using re-shaping, the second recordset can use the cached sales data.

Building on the first example, the following code creates a second hierarchical recordset (rs2) with the Customers table as the parent and the cached Orders information as the child. The code prints the Orders for the selected customer:


  Set rs2 = New ADODB.Recordset
  rs2.Open "SHAPE {SELECT * FROM Customers} APPEND (Orders AS Orders RELATE CustomerID TO CustomerID)", cn, adOpenStatic, adLockBatchOptimistic, adCmdText
  rs2.Find "CustomerID='WOLZA'"
  Set rsChild = rs2!Orders.Value
  Do While Not rsChild.EOF
    Debug.Print rsChild!OrderID, rsChild!CustomerID, rsChild!Employeeid
    rsChild.MoveNext
  Loop
  Set rsChild = Nothing
  rs2.Close
  rs.Close
  cn.Close

To build a sample application from the code fragments given above, create a new Visual Basic Standard EXE project and add a reference to Microsoft ActiveX Data Objects 2.1 Library. Paste all three code segments in order into a procedure and run it.

Additional query words:

Keywords : kbADO kbDatabase kbJET kbOLEDB kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbhowto