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
"Scott" <(E-Mail Removed)> wrote in message
news:1969e01c44cb8$a7c4c300$(E-Mail Removed)...
> 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
> .
>
>
|
Similar Threads
