Fabricating Hierarchical Recordsets

The following example shows how to fabricate a hierarchical Recordset without an underlying data source by using the data shaping grammar to define columns for parent, child, and grandchild Recordsets.

To fabricate a hierarchical Recordset, you must specify the Microsoft Data Shaping Service for OLE DB (ADO Service Provider) (MSDataShape), and you can specify a Data Provider value of NONE in the connection string parameter of the Open method of the Connection object. For more information, see Required Providers for Data Shaping.

Dim cn As New ADODB.Connection  
Dim rsCustomers As New ADODB.Recordset  
  
cn.Open "Provider=MSDataShape;Data Provider=NONE;"  
  
strShape = _  
"SHAPE APPEND NEW adInteger AS CustID," & _  
            " NEW adChar(25) AS FirstName," & _  
            " NEW adChar(25) AS LastName," & _  
            " NEW adChar(12) AS SSN," & _  
            " NEW adChar(50) AS Address," & _  
         " ((SHAPE APPEND NEW adChar(80) AS VIN_NO," & _  
                        " NEW adInteger AS CustID," & _  
                        " NEW adChar(20) AS BodyColor, " & _  
                     " ((SHAPE APPEND NEW adChar(80) AS VIN_NO," & _  
                                    " NEW adChar(20) AS Make, " & _  
                                    " NEW adChar(20) AS Model," & _  
                                    " NEW adChar(4) AS Year) " & _  
                        " AS VINS RELATE VIN_NO TO VIN_NO))" & _  
            " AS Vehicles RELATE CustID TO CustID) "  
  
rsCustomers.Open strShape, cn, adOpenStatic, adLockOptimistic, -1  

As soon as the Recordset has been fabricated, it can be populated, manipulated, or persisted to a file.

See Also

Accessing Rows in a Hierarchical Recordset
Formal Shape Grammar
Required Providers for Data Shaping
Shape APPEND Clause
Shape Commands in General