You are asking for possible alternative strategies that may avoid this
problem?
1. Data structure
A query with 12 stacked queries under it? Is this because you have a
separate table for each month of the year or something? Should these tables
be one table, with a field to distinguish records?
The use of UNION queries also raises the question of whether that data could
be combined into one table.
2. Crosstab
Would a crosstab query be able to generate the kind of matrix you need?
3. Joins
Would it be possible to use outer joins to combine some of your multiple
layers of queries into one query?
4. Subqueries
These will still use some resources, but if they eliminate the need for some
of the layers they may help. A subquery is a complete SQL statement
returning a single column (and usually a single row) within a query. For
example, use with EXISTS or ANY to see if there a related value.
5. Domain aggregate functions
DLookup() etc are heavy on resources (and a performance disaster) when used
at every row of a query. Replace with joins, subqueries, or other approaches
if possible. If there is no alternative, there is a replacement for
DLookup() here:
http://allenbrowne.com/ser-42.html
It runs about twice the speed of DLookup() and does clean up after itself,
but is still inferior to SQL-based alternatives.
6. Temp table
Where there is no other solution, you could always write your data into a
temp table before loading the form. This approach breaks the problem into
two, effectively giving you double the capacity before you hit the wall. You
can usually break down a complex problem at a point that gives you a great
performance boost as well.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Koen" <no@spam.nl> wrote in message
news:Xns9463E8BA05C88Rubends@194.109.133.20...[color=blue]
>
> At work I created a database which is really helpful. The database is used
> by approx 15 users. Everything worked great, until I added some
> 'scoreboard' forms and reports. I get the "Cannot open any more databases"
> error.
>
> The 'scoreboard' form show a matrix of 6 columns, 7 rows. Each cell is
> calculated separate by (what I call complex) queries.
>
> For example, the cells in 2 of these rows (so were are talking about 12
> cells) are 'calculated' by a query each. Each of these queries are based[/color]
on[color=blue]
> 12 other queries. Each of those queries are based on 2 other queries, one
> based on 2 tables the otherone based on 2 different queries: the 1st based
> on 2 UNION queries the 2nd based on a query, based on 2 tables. (Maybe,[/color]
you[color=blue]
> can see why I call it complex)
>
> Anyhow, I read about what is written about the error in this group before.
> I understand that I probably create this problem by using too much table
> handles. I understand that the limit of this resources is 2048. The odd
> thing is that when I try to add up the amount of table handles in this
> form, I come up with approx. 400, but I guess I must be wrong.
>
> I want to learn from this and I am looking for a good strategy to solve
> this problem. Having so much (nested) queries was/is really the only
> solution I can think of to create my beloved 'scoreboard' form. So, my
> question is:
>
> 1. Is my 'table handle' guess right or could something else cause this
> problem?
>
> 2. What is the appropriate way to create a complex form or report (with
> lots of different queries) and remain efficient with resources (table
> handles)? How is it done? What's a good strategy? What are the do's and
> don'ts?
>
> I welcome all your suggestions![/color]
"Cannot open any more databases": strategy needed 
