If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Too many tables
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 |
#2
|
|||
|
|||
Too many tables
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Ian Belcher" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|