Opened Recordsets don't show up in Recordsets Collection 
Author Message
 Opened Recordsets don't show up in Recordsets Collection

I need to be able to indirectly address recordsets.  I thought I could do
it by referencing the recordsets through the recordsets collection.  But,
even though I have opened the 5 recordsets which refer to tables in the
current database using the openrecordset method on the currentdb object,
the recordsets count is 0.  I have noticed that if I open recordsets in a
database other than the current one, that database recordsets.count is
greater than 0.  What am I missing here?


Tue, 30 Apr 2002 03:00:00 GMT  
 Opened Recordsets don't show up in Recordsets Collection
CurrentDB always returns a new instance of the database object.  That's
basically the cause of the problem you are experiencing.... Hopefully, this
code sample should explain all...

' ******* Code Start *********
Sub sTestRS()
    Dim db As Database
    Dim rs As Recordset

    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("Select * From MSysObjects", dbOpenSnapshot)
    With DBEngine(0)(0)
        Debug.Print .Recordsets.Count,
        Debug.Print .Recordsets(rs.Name).Name
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * From MSysObjects", dbOpenSnapshot)
    With db
        Debug.Print .Recordsets.Count,
        Debug.Print .Recordsets(rs.Name).Name
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * From MSysObjects", dbOpenSnapshot)
    With CurrentDb
        Debug.Print .Recordsets.Count,
        Debug.Print .Recordsets(rs.Name).Name
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub
' ********* Code End ********

 -- Dev


Quote:
> I need to be able to indirectly address recordsets.  I thought I could do
> it by referencing the recordsets through the recordsets collection.  But,
> even though I have opened the 5 recordsets which refer to tables in the
> current database using the openrecordset method on the currentdb object,
> the recordsets count is 0.  I have noticed that if I open recordsets in a
> database other than the current one, that database recordsets.count is
> greater than 0.  What am I missing here?



Tue, 30 Apr 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Setting recordset in Access 2000/XP (Set Forms!frmX.recordset = Forms("frmXXXX").recordset

2. Don't know how to access recordset

3. Can't open Recordset...

4. Can't Open Recordset with Criteria

5. can't open recordset in form code

6. opening a sub-recordset on form's recordsource

7. Can't open VBA Recordsets

8. Can't open recordset, please help

9. can't open adodb.recordset with union query

10. Recordset.Open using an SQL string doesn't return any records


 
Powered by phpBB® Forum Software © phpBB Group