So you want to make your own Recordset/Table that does not get stored anywhere?

Here is an example of that. This RS is created and manipulated by my code, but not physically stored anywhere (except in memory, of course)

Code:
    Dim rs As ADODB.Recordset
    Dim lngCounter As Long
    
    Set rs = New Recordset
    
    'create my temp recordset
    With rs
        .CursorLocation = adUseClient
        
        .Fields.Append "FirstName", adVarChar, 50
        .Fields.Append "LastName", adVarChar, 50
        
        .Open
        
        For lngCounter = 1 To 10
            'add my records, just some garbage values
            .AddNew
            .Fields("FirstName") = "Tom" & lngCounter
            .Fields("LastName") = "Clunie" & lngCounter
            .Update
        Next lngCounter
        
        'shows we have 10 recs
        MsgBox .RecordCount
        
        'show value of field
        MsgBox .Fields(0)
        
        'go to first record
        .MoveFirst
        
        'show that value
        MsgBox .Fields(0)
        
        
    End With