Closing QueryDef doesn't close databases

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hello,

I'm really stuck on this so I hope someone can help. I'm
getting "cannot open any more databases" in Access 2003.
I've tracked it down to the point where the number of
databases increases when I create a QueryDef but it
doesn't decrease when I close it.

Here is the basic code:

Dim FINDER_QDF As DAO.QueryDef
Dim FINDER As Form
DIM SQL As String
Set FINDER = Forms!TABS!TABS_sub_FIND.Form
Set FINDER_QDF = CurrentDb.CreateQueryDef("")
SQL = "Select ..."
FINDER_QDF.SQL = SQL
Set FINDER.Recordset = FINDER_QDF.OpenRecordset
(dbOpenDynaset)
FINDER_QDF.Close
Set FINDER_QDF = Nothing

In the immediate window, I check DbEngine.Workspaces
(0).databases.count at various times. The value
increases from 4 to 5 when I do "Set FINDER_QDF = ..."
yet it doesn't go back to 4 after I close the QueryDef.
It seems like this accumulation of databases is the
direct cause of my "cannot open any more databases" after
a while.

Any ideas why this is happening?

Scott
..
 
Not sure this is the problem but why are you reusing the QueryDef. What if
you declare a separate record set object? For instance:

Dim FINDER_QDF As DAO.QueryDef
Dim rst As DAO.Recordset
Dim FINDER As Form
Dim SQL As String

Set FINDER = Forms!TABS!TABS_sub_FIND.Form
Set FINDER_QDF = CurrentDb.CreateQueryDef("")
SQL = "Select ..."
FINDER_QDF.SQL = SQL
Set rst = FINDER_QDF.OpenRecordset(dbOpenDynaset)
....
rst.Close
Set rst = Nothing
FINDER_QDF.Close
Set FINDER_QDF = Nothing

or if you do not plan on saving the query you could:


Dim dbs As Database, rst As DAO.Recordset
Dim sql as String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)

Dan
 
Back
Top