Recordset.Clone Method Gives Type Mismatch Error

[Norris68]

I have an Access 2000 form that has been filtered using the Filter By Form. I am now trying to create a clone of the resulting recordset so I can iterate through and find out the Primary Key values (without repositioning my form). When I try to set my ADODB.Recordset to the Me.Recordset.Clone method output I get a Type Mismatch error. I should not be returning DAO recordsets, as the library is not loaded - we are working in ADO only.

    Dim R As ADODB.Recordset
    Criteria = "orgID IN ("
    Set R = Me.Recordset.Clone 'Error occurs here
    While Not R.EOF
        Criteria = Criteria & R("orgID").Value & ", "
        R.MoveNext
    Wend
    Set R = Nothing
    Criteria = Left$(Criteria, Len(Criteria) - 2) & ")"

 

[Norris68]

OK, I'll answer it for myself.

Access will always return a DAO recordset within a MDB project even though a brand new database will not reference DAO by default. You cannot avoid it unless you are using an ADP connecting to SQL data!

To work around it without having to reference DAO, create the Recordset object as a Variant (inefficient, but will only exist fleetingly).

    Dim R As Variant 'Actually a DAO.recordset
    Criteria = "orgID IN ("
    Set R = Me.Recordset.Clone 'This now works
    While Not R.EOF
        Criteria = Criteria & R("orgID").Value & ", "
        R.MoveNext
    Wend
    Set R = Nothing
    Criteria = Left$(Criteria, Len(Criteria) - 2) & ")"

 

[HomeALone]

Norris68

Thanks for answering yourself! I've learned a good lesson today and avoid waiting my time on it.