Building an ADODB recordset in memory

So I got do do a bit of VBA today <sarcasm>YEAH!</sarcasm>. Anyway I have this whole thing written that allows excel to report out of Documentum which is all well and good until Documentum reports more rows than excel can handle. In any case for this thats neither here nor there. Anyway I was working on this interface a thought "You know it would be nice not to have to read a spreadsheet when i want to use the results" and decided that I would write a function that would write the Documentum query results to an ADO recordset.

Public Function ExecuteReader(ByVal sql As String) As ADODB.Recordset
    Dim rs As IDfCollection
    Dim ado As Recordset
    Dim acount As Integer
    Dim i As Integer
    Dim exception As String
    docBase = "docbasename"
    connected = DQL.connect(conn, docBase)
    Set rs = conn.queryDocbase(sql, exception)
    If Len(exception) > 0 Then
        fail = MsgBox(exception & vbCrLf & vbCrLf & "Press Ok to continue or Cancel to Quit", vbOKCancel)
        exception = ""
        If fail = 2 Then Exit Function
    End If
    If Not rs Is Nothing Then
        acount = rs.getAttrCount - 1
        Dim fields() As Variant
        ReDim fields(acount)
        Set ado = New ADODB.Recordset
        ado.CursorLocation = adUseClient
        For i = 0 To acount
            ado.fields.append rs.GetAttr(i).getName(), adVariant
            fields(i) = rs.GetAttr(i).getName()
        Next i
        ado.Open , , adOpenStatic, adLockBatchOptimistic
        Do While rs.Next
            Dim values() As Variant
            ReDim values(acount)
            For i = 0 To acount
                values(i) = rs.getValueAt(i).asString
            Next i
            ado.AddNew fields, values
            ado.Update
        Loop
        Set ExecuteReader = ado
    End If
    Call DQL.disconnect(conn, connected)
    Call cleanUp
End Function

Im sure most of this makes little to no sense so Ill break down the parts that are relevant.

• If you have done any VBA you probably already know that in order to use an ADODB.Recordset you should dim it and create an instance of the object.
  

  Dim ado As Recordset
  Set ado = New ADODB.Recordset

• The next thing you should do is create two arrays one for the column names and one for values. If you need to dynamically resize the arrays you can use the ReDim command as I have.
  

  Dim fields() As Variant
  ReDim fields(19)
  Dim values() As Variant
  ReDim values(19)

• At this point you can add your column names and as you can see they can be done dynamically. Its a good idea to populate your array of column names at the same time.
  

  ado.fields.append "Column1", adVariant
  ado.fields.append "Column2", adVariant
  ado.fields.append "Column3", adVariant
  
  fields(0) = "Column1"
  fields(1) = "Column2"
  fields(3) = "Column3"

• Next set your cursor and open the blank recordset (I am not sure if its necessary to set the cursor but it sounds good ;P and I got the idea from this page)
  

  ado.CursorLocation = adUseClient
  ado.Open , , adOpenStatic, adLockBatchOptimistic

• Last add your data rows by populating the values array and passing the arrays to the AddNew method.
  

  values(0) = "Value1"
  values(1) = "Value2"
  values(3) = "Value3"
  
  ado.AddNew fields, values
  ado.Update

Now the record set can be used just like if the data were to come out of Access or SQL Server.