Microsoft KB Archive/249097

= How To Re-Shape a Hierarchical Recordset =

Article ID: 249097

Article Last Modified on 7/2/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q249097



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
Using the re-shaping feature of the MSDataShape 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:

249027 How To 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:

189657 How To 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 preceding code segments, create a new Visual Basic Standard EXE project and add a reference to the Microsoft ActiveX Data Objects Library. Paste all three code segments in order into a procedure and run it.

Keywords: kbhowto kbdatabase kbjet KB249097

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.