I have a small project at work that requires me to assign an ADO recordset to the field value of another ADO recordset. Both of these recordsets are in-memory recordsets that I create in code. This would seem to be the perfect situation for a hierarchical recordset; but, I don't know how to create one without writing it against a table. Below is some code that I wrote for testing the concept:

Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

rs1.Fields.Append "Field1", adBSTR, 50, adFldIsNullable
rs1.Fields.Append "Field2", adBSTR, 50, adFldIsNullable
rs1.Fields.Append "FieldRS", adChapter

rs2.Fields.Append "Field1", adBSTR, 50, adFldIsNullable
rs2.Fields.Append "Field2", adBSTR, 50, adFldIsNullable

rs2.Fields(0) = "RS 2 Field 1"
rs2.Fields(1) = "RS 2 Field 2"

rs1.Fields(0) = "RS 1 Field 1"
rs1.Fields(1) = "RS 1 Field 2"
Set rs1.Fields(2).Value = rs2

I get an error on line 3 when I try to create the field to hold the second recordset. I would appreciate any suggestions, tips or help that can be provided. Thanks in advance.

Greg

I went googling and found a code sample that demonstrates how to create an in-memory ADO hierarchical recordset.

Dim rs As New ADODB.Recordset
Dim rsCh As ADODB.Recordset
Dim rsGrndCh As ADODB.Recordset

         rs.ActiveConnection = "provider=msdatashape;data provider=none;"

         rs.Open " SHAPE APPEND new adInteger As PID, " & _
               " New adVarChar(10) As StudentName, " & _
               "((SHAPE APPEND new adInteger As ChID, " & _
               " New adVarChar(10) As Course, " & _
               "((SHAPE APPEND new adInteger As GrndChID, " & _
               " New adBSTR As Description) RELATE " & _
               " ChID TO GrndChID) As GrandChild) RELATE PID TO ChID) " & _
               "AS Child" , , adOpenStatic, adLockOptimistic

          ' Add a sample record in the parent recordset
          rs.AddNew Array("PID", "StudentName"), Array(1, "Jim Smith")

          ' Now add a two sample child records related to the original
          ' parent's record
          Set rsCh = rs("Child").Value
          For i = 0 To 1
             rsCh.AddNew Array("ChID", "Course"), Array(1, "Course #1" & i)

             ' Now add two sample Grand-child records for each child record
             Set rsGrndCh = rsCh("GrandChild").Value
             For j = 1 To 2
                rsGrndCh.AddNew Array("GrndChID", "Description"), _
                      Array(i, "Description" & Str(j))
             Next
          Next

          Set MSH1.DataSource = rs

          MsgBox "Successfully Done..."

          rs.Close
          rsCh.Close
          rsGrndCh.Close

Greg