2

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.

Improve this question

3 Answers 3

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Have you tested your code? It doesn't work for me. For one thing, there is no .SQL property in a DAO.Recordset. (The SQL, if any, is in the .Name property.)
Failure to declare the DB variable might be causing the bug.
The code now conforms to your instructions, except I am declaring the database object locally. The recordset and database are cldeaned up. The behaviour is just the same. I don't have any reason now to think the problem is multiple instantiations of the recordset. I guess I'll start a new post.
3

Open the Locals Window.

Open Locals Window

Then you can monitor variables as you run the code.

Locals Window

Improve this answer

1 Comment

Thank you, HansUp. I almost made mention in the OP of my phobic avoidance of the Locals Window. :-P
2

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

Improve this answer

9 Comments

This is because: The CurrentDb method creates another instance of the current database, while the DBEngine(0)(0) syntax refers to the open copy of the current database. The CurrentDb method enables you to create more than one variable of type Database that refers to the current database.
@4dmonster Strange, but a Watch on DBEngine(0)(0).Recordsets also shows nothing....
Have you changed Set cdb = CurrentDb to set Set cdb = DBEngine(0)(0) ? Or if you have kept it you should Watch on DBEngine(0)(1).Recordsets
I have missed answer update. Try to check DBEngine(0)(1).Recordsets in last case. Because call to Currentdb created new reference an it have (1) index.
I put dao. as a prefix on Database. VBE failed to autocorrect to DAO.. So I altered the library refs -- removed Microsoft Office 14.0 Access Daabase Engine Object Library and put in 'Microsfot DAO 3.6 Object Library'. The syntax was auto-corrected... bug stays the same... but, am I onto something?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.