In Memory, Stand-Alone, Disconnected ADO Recordset

Question

I'm running this code on my datasheet subform when my form loads and I'm not getting any error messages or code breaks. My debug.print shows that the Recordset rs is filled with 2131 records like it should be, but my form shows a single row with #Name? in every field. The control source properties on my controls most certainly do match the field names I have listed above. RS is a form level variable and I'm not closing it or setting it to nothing until the form closes.

Any idea what am I doing wrong?

Set rs = New ADODB.Recordset
rs.Fields.Append "TimesUsed", adInteger
rs.Fields.Append "strWorkType", adVarWChar, 150
rs.Fields.Append "DateLastUsed", adDate
rs.Fields.Append "SelectedYN", adBoolean
Set rs.ActiveConnection = Nothing
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.Open

Dim sSQL As String
sSQL = "MyComplicated SQL Statement Ommitted from this SO Question"

Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
If Not (r.EOF And r.BOF) Then
    r.MoveFirst
    Dim fld
    Do Until r.EOF = True
        rs.AddNew
        For Each fld In r.Fields
            rs(fld.Name) = r(fld.Name).value
        Next
        rs.Update
        r.MoveNext
    Loop
End If
r.Close
Set r = Nothing
Debug.Print rs.RecordCount '2131 records
Set Me.Recordset = rs

OK, so I just read this on the MSDN site:

The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.

(Note: This information seems to be erroneous in this context.)

Answer 1

is it possible to setup a primary key on a recordset that is only an in-memory object?

Yes, use adFldKeyColumn as the Attrib to the Append Method. Read about FieldAttributeEnum for more details.

If you already have a suitable unique field (or combination of fields) available from your SQL statement, use that. If not, create a long integer field and use it as a fake primary key field ... increment the value for each row you insert.

rs.Fields.Append "pkey", adInteger, , adFldKeyColumn

Also see if this article from Database Journal by Danny Lesandrini is helpful: Create In-Memory ADO Recordsets