CODEWITHSUNDEEP
vbams-accessrecordset
Smandoli
Smandoli

Reputation: 7019

VBA debugging -- see all open DAO recordsets

I am working on an Access 2010 database in which a DAO recordset is used to create new record. However the code fails to ID the newly-created record -- it finds another record.

The developer was sloppy: he doesn't close recordsets. The code loops around a few times and I'm fairly certain multiple instances of the recordset are the cause of the problem. 

Set rsMain = CurrentDb().OpenRecordset(strSQL, dbOpenDynaset)
         '      ... create new record ....
'rsMain.Close          '' not included, unfortunately
'Set rsMain = Nothing  '' not included, unfortunately

I want to Stop the code and see a list of open DAO recordsets. How can I use the Immediate window to query for all open recordsets? There must be a collection ready to view.

Resolved

I accepted an answer as helpful because it did a great job addressing my post. While I benefited, the answer I needed lay in a different direction -- VBA Object References. See my comments under Gord's answer.

Upvotes: 2

Views: 5093

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123654

This is one of those cases where failure to use an actual DAO.Database object can lead to confusion. Consider the following code:

Sub liminal()
    Dim cdb As DAO.Database, rst As DAO.Recordset
    Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset("SELECT * FROM Clients", dbOpenSnapshot)
    Debug.Print "-----"
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
End Sub

If I set a breakpoint on the Debug.Print line and then run the code I can open the Watch Window to see what's going on. If I create a Watch for CurrentDb.Recordsets it shows nothing:

noCdb.png

but if I create a Watch for cdb.Recordsets I can see the Recordset I created

cdb.png

edit re: comment

Interestingly, a Watch on DBEngine(0)(0).Recordsets also shows nothing:

dbe.png

Upvotes: 2

4dmonster
4dmonster

Reputation: 3031

It depends on the way you open recordsets.

"A Recordsets collection contains all open Recordset objects in a Connection or Database object."

You should save CurrentDB() into variable, in order not to loose info, since each call to it creates new Database reference.

Or declare something like this function that should replace all your CurrentDB() calls^

Public Function CurrDB() As Database
    Static mCurrDb As Database
    If mCurrDb Is Nothing Then
        Set mCurrDb = CurrentDb 
        Debug.Print Now, "static mCurrDB inited for ", hWndAccessApp
    End If
    Set CurrDB = mCurrDb
End Function

and in code from Q:

Set rsMain = CurrDB().OpenRecordset(strSQL, dbOpenDynaset)
         '      ... create new record ....
'rsMain.Close          '' not included, unfortunately
'Set rsMain = Nothing  '' not included, unfortunately

and for debug output write function as:

Public Sub  OpenedRST()
    dim rst as DAO.Recordset

    For each rst in CurrDB().Recordsets
       debug.print rst.name
    next rst
end sub

Upvotes: 4

HansUp
HansUp

Reputation: 97101

Open the Locals Window.

Open Locals Window

Then you can monitor variables as you run the code.

Locals Window

Upvotes: 3

Related Questions