[AccessD] ADO.Recordset "Object does not support Automation"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi everybody,

Most of my project runs on DAO, but there's one place where I'd like to
back a listbox with a recordset returned by a SQL Server stored
procedure that returns multiple recordsets.  (This is the browse control
from my last post, which I finally managed to convince people shouldn't
be a monolithic 50,000 row list box.)

It seems that
- - DAO recordsets in Jet workspaces don't support the NextRecordset
method, so multiple result sets are not accessible
- - The docs say that DAO recordsets from ODBCDirect workspaces can't be
assigned to the .Recordset property of a ListBox control

So I have to use an ADO recordset.  So I get the connection going and
the query runs and the recordset comes back okay:

Public Sub InitializeByOffset(filterString As String, offset As Long)
    Dim sql As String
    sql = ' some SQL text lives here
    Dim cxn As ADODB.Connection
    Set cxn = Util.ADOConn ' this returns a cached ADO connection
    Dim rs As New ADODB.Recordset
    rs.Open sql, cxn, adOpenKeyset
    rs.MoveFirst
    ' initialize some properties of the object from the first
    ' recordset's rows
    ' now remember the second recordset
    Set mRecordset = rs.NextRecordset
End Sub

That all works fine, and the "next recordset" returned is alive and has
the right data in it.  Then when I try to assign the resulting recordset
to the .Recordset property of the ListBox control on my form, I get the
following error:

"Class does not support Automation or does not support expected interface".

This is Access 2002 SP3 on Windows XP SP2; my installed version of MDAC
is 2.81 (2.8 SP1); the active set of References in the VB editor is as
follows:

Visual Basic For Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
  [C:\Program Files\Common Files\System\ado\msado15.dll]
Microsoft Data Access Components Installed Version
  [C:\WINDOWS\system32\odbcconf.dll]
Microsoft ActiveX Data Objects Recordset 2.8 Library
  [C:\Program Files\Common Files\System\ado\msador15.dll]

Googling suggests that the problem has something to do with out-of-sync
MDAC versions, but I have tried substituting all the other available ADO
references in the VB project (2.1, 2.5, 2.6, 2.7) without success.

Can anyone tell me what's going on here?

many thanks for all your help,
Chris Jeris

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGs5h05ICCNV0oGWARAkUzAJ9rRO4Q5dpJFip4gaesi+KBjZCopwCglBy8
bu1IYkzwc/mubdZeRIPIjmE=
=i6T1
-----END PGP SIGNATURE-----