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