HandsOn Creating a Shaped Recordset with Multiple Children

^^ Note: Because this hands-on retrieves data from the Northwind database, adjust the path found in the procedure code to point to the correct location of this file on your computer.

1. Insert a new module and, in the module's Code window, enter the ShapeMultiChildren procedure as shown below.

Sub ShapeMultiChildren()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim rstChapter1 As Variant Dim rstChapter2 As Variant Dim strConn As String Dim shpCmd As String Dim strParent As String Dim strChild1 As String Dim strChild2 As String Dim strLink As String Dim str1stChildName As String Dim str2ndChildName As String

' define database connection string ' using the OLE DB provider ' and Northwind database as Data Source strConn = "Data Provider=Microsoft.Jet.OLEDB.4.0;" strConn = strConn & "Data Source = " & _

CurrentProject.Path & "\Northwind.mdb"

specify Data Shaping provider

Part II

' and open connection to the database

Set conn = New ADODB.Connection

With conn

.ConnectionString = strConn .Provider = "MSDataShape" .Open

End With

' define the SHAPE command for the shaped recordset strParent = "SELECT CustomerID as [Cust Id], " & _ "CompanyName as Company FROM Customers"

strChild1 = "SELECT CustomerID, OrderDate," &_ "OrderID, Freight FROM Orders"

strChild2 = "SELECT Customers.CustomerID," &_ "Products.ProductName FROM Products " & _ "INNER JOIN ((Customers INNER JOIN Orders ON " & _ "Customers.CustomerID = Orders.CustomerID) " & _ "INNER JOIN [Order Details] ON " & _ "Orders.OrderID = [Order Details].OrderID) ON " & _ "Products.ProductID = [Order Details].ProductID " & _ "Order By Products.ProductName"

str1stChildName = "custOrders"

str2ndChildName = "custProducts"

strLink = "RELATE [Cust Id] To CustomerID"

shpCmd

=

"SHAPE

{"

shpCmd

=

shpCmd

& strParent

shpCmd

=

shpCmd

& "}"

shpCmd

=

shpCmd

& " APPEND ({"

shpCmd

=

shpCmd

& strChild1

shpCmd

=

shpCmd

& "}"

shpCmd

=

shpCmd

& strLink

shpCmd

=

shpCmd

& ")"

shpCmd

=

shpCmd

& " AS " & str1stChildName

shpCmd

=

shpCmd

& ", ({"

shpCmd

=

shpCmd

& strChild2

shpCmd

=

shpCmd

& "} "

shpCmd

=

shpCmd

& strLink

shpCmd

=

shpCmd

& ")"

shpCmd

=

shpCmd

& " AS " & str2ndChildName

' create and open the parent recordset ' using the open connection Set rst = New ADODB.Recordset rst.Open shpCmd, conn

' output data from the parent recordset Do While Not rst.EOF

Creating and Manipulating Databases with ADO

Debug.Print rst("Cust Id"); Tab; rst("Company") rstChapter1 = rst("custOrders")

' write out column headings ' for the 1st child recordset

Debug.Print Tab(4); " (" & rst("Cust Id") & " Orders)" Debug.Print Tab; "OrderDate", "Order #", "Freight"

' output data from the 1st child recordset Do While Not rstChapter1.EOF Debug.Print Tab; _

rstChapter1("OrderDate"), _ rstChapter1("OrderID"), _ Format(rstChapter1("Freight"), "$ #,#0.00") rstChapter1.MoveNext

Loop rstChapter2 = rst("custProducts") ' write out column headings ' for the 2nd child recordset

Debug.Print Tab(4); " (" & rst("Cust Id") & " Products)"

' output data from the 2nd child recordset Do While Not rstChapter2.EOF Debug.Print Tab; _

rstChapter2("ProductName") rstChapter2.MoveNext

Loop rst.MoveNext

Loop

' Cleanup rst.Close Set rst = Nothing Set conn = Nothing End Sub

The SHAPE statement in the above procedure has been specially formatted so that you can easily create any shaped recordset containing multiple children by replacing SELECT statements with your own. This procedure produces the output in the Immediate window as shown in Figure 16-7. Notice that each customer has two child records: Orders and Products.

Part II

CHLFKI Orders)

OrderDate 3/25/199 71 10/3/199T 13/13/1991" 1/15/1993 3/13/1993 1/9/1993

Order 8 10343 10392 10702 10335 10952 11011

Freight Î 29.HB Î 31.02 Î 23.94 Î 39.53 Î 48.42 î 1 .21

Figure 16-7: After running the ShapeMultiChildren proce-

CHLFKI Orders)

OrderDate 3/25/199 71 10/3/199T 13/13/1991" 1/15/1993 3/13/1993 1/9/1993

Order 8 10343 10392 10702 10335 10952 11011

Freight Î 29.HB Î 31.02 Î 23.94 Î 39.53 Î 48.42 î 1 .21

dure in Hands-On 16-5, you can see the output of the hierarchical recordset with multiple children in the Immediate window.

(ALFK.I Products)

Aniseed Syrup Chartreuse uerte Escargots de Bourgogne Flatemysost

Grandma ' s Boysentjerry Spread Lakkaiikoori

Original Frankfurter grüne Soße

Raclette Courdauault

Rö33le Sauerkraut

Rö33le Sauerkraut

Spege3ild

Uegie-spread

Shaped Recordsets with Grandchildren

In addition to the parent recordset having multiple children, the child recordset can contain a child of its own. Simply put, your hierarchical recordset can contain grandchildren. Creating such a hierarchy is a bit harder, but it can be tackled in no time if you take a step-by-step approach. The SHAPE syntax that includes grandchildren looks like this:

SHAPE {SELECT * FROM Parent} APPEND (( SHAPE {SELECT * FROM Child} APPEND ({SELECT * FROM Grandchild}

RELATE child-column TO grandchild-column) AS grandchild-alias) RELATE parent-column TO child-column) as child-alias

Notice that when grandchildren are present, the child recordset is appended with another SHAPE command.

Although you can have as many children or grandchildren as you want, it will be more difficult to write a SHAPE statement that uses more than three or four levels.

0 0

Post a comment