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:Xn*********************@194.109.133.20...
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
on 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,
you 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!
