HandsOn Creating a Shaped Recordset

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

Sub ShapeDemo()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim rstChapter As Variant Dim strConn As String Dim shpCmd 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 = C:\Program Files\" & _ "Microsoft Office\Office11\Samples\Northwind.mdb"

' specify Data Shaping provider ' 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 shpCmd = "SHAPE {SELECT CustomerID as [Cust Id], " & _ " CompanyName as Company FROM Customers}" & _ " APPEND ({SELECT CustomerID, OrderDate," &_ " OrderID, Freight FROM Orders} AS custOrders" & _ " RELATE [Cust Id] To CustomerID)"

' 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

Debug.Print rst("Cust Id"); _ Tab; rst("Company") rstChapter = rst("custOrders") ' write out column headings ' for the child recordset

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.

Creating and Manipulating Databases with ADO

Debug.Print Tab; _

"OrderDate", "Order #", "Freight" ' output data from the child recordset Do While Not rstChapter.EOF Debug.Print Tab; _

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

Loop rst.MoveNext


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

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

Figure 16-6: After running the ShapeDemo procedure in Hands-On 16-4, you can see the contents of the hierarchical recordset in the Immediate window.

Let's take a few minutes to examine the ShapeDemo procedure in Hands-On 16-4. This procedure begins by specifying the data provider and data source name in the strConn variable. Next, we define a new ADO connection object and set the ConnectionString property of this object to the strConn variable. Now that we have the data provider name and also know which database we need to pull the data from, we go on to specify the data shaping service provider. This is done by using the Provider property of the Connection object. We set this property to MSDataShape, which is the name of the service provider for the hierarchical recordsets. Now we are ready to actually open a connection to the database. Before we can pull the required data from the database, we define the shaped recordset statement and store it in the ShpCmd String variable. Next, we create a new Recordset object and open it using the open database connection. Then, we populate it with the content of the ShpCmd variable like this:

Set rst = New ADODB.Recordset rst.Open shpCmd, conn

Part II

Now that we have filled the hierarchical recordset, we begin to loop through the parent recordset. The first statement in the loop

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

will write out the customer ID (Cust Id) and the company name (Company) to the Immediate window.

In the second statement in the loop rstChapter = rst("custOrders")

we create a Recordset object variable based on the value of the custOrders field.

As you recall from an earlier discussion, custOrders is an alias for the child recordset. The Object variable (rstChapter) can be any name you like as long as it's not a VBA keyword.

^^ Note: Because a child recordset is simply a field in a parent recordset, when you retrieve the value of that field you will get the entire recordset filtered to include only the related records.

Before iterating through the child recordset, the column headings are output to the Immediate window for the fields we want to display. This way it is much easier to understand the meaning of the data in the child recordset. The next block of code loops through the child recordset and dumps the data to the Immediate window under the appropriate column heading. Once the data is retrieved for each parent record, we can close the recordset and release the memory.

0 0

Post a comment