Sample Project with Shaped Data
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
