Too many tables

[Ian Belcher]

Hi,
I have a query that uses several subqueries. If I run this query several
times I get the error of too many tables open. I don't have any tables open,
and I only have this one query open. The only way around it is to close down
the database and to start again. I'm thinking that for some reason the
subqueries are remaining in the memory somewhere and aren't being cleared.
It's frustrating because I have 150 reports based on the query which I
export to pdf (thanks Stephen Lebans - fantastic bit of code!) but I have to
restart the process every few reports.
Is this a known error, or am I doing something wrong?

Thanks

Ian

 

[Jerry Whittle]

Some guesses:

The data isn't normalized properly which requires some rather complicated
queries. I'm going to further assume that redesigning the database isn't a
viable option.

You might want to consider using temporary or 'work' tables to gather up
some of the data first. Run these make table or delete/append queries first
to gather up some of the data formerly gathered in the subqueries.

Another option would be to open the queries as a recordset in a function.
Then you could close the recordset and set it to Null. That should clear it
out of memory.