Sample Project with Shaped Data

by David Faour

Download Code and Program

---

Previously, I discussed the basics of creating Shaped ADO Recordsets. Now, we will explore the use of the detail records through the examination of a sample project.

Consider the following SHAPE statement.

Previously, I discussed the basics of creating Shaped ADO Recordsets. Now, we will explore the use of the detail records through the examination of a sample project.

Consider the following SHAPE statement.

SHAPE {SELECT * FROM Customers} AS Customers
    APPEND ({SELECT * FROM Orders} 
     RELATE customerID TO customerID) AS Orders 

Accessing the "Orders" field is as simple as set adoRSDetail=adoRSMaster("Orders").Value That's it! There's nothing to it.

As you might have imagined, these data shapes can be nested. What that means is that the "Child" recordset returned in the "Orders" field, could have a "GrandChild" recordset called "OrderDetails"

It would look something like the following.

SHAPE {SELECT * FROM Customers} AS Customers 
   APPEND (Shape {SELECT * FROM Orders} as Orders
Append {Select * from OrderDetails} as OrderDetails 
Relate OrderID to OrderID as OrderDetails)
     RELATE customerID TO customerID) AS Orders 

And so on!

Ok, now for the sample project.

• Create a form "Form1"
• Create two listboxes "lbCustomer" and "lbOrders"
• Create a command button "cmdShow"
• Create a DSN call "Northwind" to the Northwind sample DB included with Access

Paste in the code below. It's a very simple demonstration, but you should get the idea. To see how it works, run the program and click a customer in the lbCustomer. Then click cmdShow, which will load the lbOrders with some information about all of that customers orders.

CODE BEGINS HERE
Option Explicit

Dim adoCN As ADODB.Connection
Dim adoRSMaster As ADODB.Recordset
Dim adoRSDetail As ADODB.Recordset
Dim strCN As String
Dim strMaster As String

Private Sub cmdShow_Click()
lbOrders.Clear
If Not lbCustomer.ListIndex = -1 Then
    
    'to move to the right record in the recordset
    adoRSMaster.AbsolutePosition = lbCustomer.ListIndex + 1
    'to access the detail recordset
    Set adoRSDetail = adoRSMaster("Orders").Value
    adoRSDetail.MoveFirst
    Do Until adoRSDetail.EOF 'to display orders for that customer
        lbOrders.AddItem adoRSDetail("CustomerID") & "|" & adoRSDetail("orderID") & "|" & adoRSDetail("OrderDate")
        adoRSDetail.MoveNext
    Loop
Else
    MsgBox "Please select a customer"
End If
End Sub

Private Sub Form_Load()
Set adoCN = CreateObject("ADODB.Connection")
Set adoRSMaster = CreateObject("ADODB.Recordset")
Set adoRSDetail = CreateObject("ADODB.Recordset")

'DSN connection
strCN = "Provider=MSDataShape;" & _
        "DSN=Northwind;"

adoCN.ConnectionString = strCN
adoCN.Open

'sample shape recordset statement
strMaster = " SHAPE {SELECT * FROM Customers} AS Customers " & _
            " APPEND ({SELECT * FROM Orders} " & _
            " RELATE customerID TO customerID) AS Orders "
   
adoRSMaster.Open strMaster, adoCN, adOpenStatic, adLockReadOnly
'to load customer listbox
adoRSMaster.MoveFirst
Do Until adoRSMaster.EOF
    lbCustomer.AddItem adoRSMaster("CustomerID")
    adoRSMaster.MoveNext
Loop




End Sub
CODE ENDS HERE