By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

"Cannot open any more databases": strategy needed

P: n/a
Hi all,

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!

Thanks,

Koen

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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!

Nov 12 '05 #2

P: n/a
Thanks for your reply. I will work on it.

The main cause is in my data structure, I think. Through time a lot of
sophisticated status info is introduced in my 'scoreboard' forms and
reports. These statusses are derived by all these nested queries. I think I
have to think about a complete rebuild of the project...

Just a few more questions about subqueries, joins and unions, how many
table handles are used in case of:

a. A query with three queries on the same table, combined in one query with
UNION?

b. A query on one table with a JOIN to the same table?

c. A query on one table with a subquery to the same table?

I hope anyone knows! Strange this is, I have a lot of Access related books,
but none of them covers this issue...

Bye, and thanks for the help!

Koen

Nov 12 '05 #3

P: n/a
Koen, I can't give you counts, but I do know that these things mount up
quickly.

I already highlighted the domain aggregate functions as an issue to try to
avoid. Unclosed recordset objects in code are another.

--
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...
Thanks for your reply. I will work on it.

The main cause is in my data structure, I think. Through time a lot of
sophisticated status info is introduced in my 'scoreboard' forms and
reports. These statusses are derived by all these nested queries. I think I have to think about a complete rebuild of the project...

Just a few more questions about subqueries, joins and unions, how many
table handles are used in case of:

a. A query with three queries on the same table, combined in one query with UNION?

b. A query on one table with a JOIN to the same table?

c. A query on one table with a subquery to the same table?

I hope anyone knows! Strange this is, I have a lot of Access related books, but none of them covers this issue...

Bye, and thanks for the help!

Koen

Nov 12 '05 #4

P: n/a
no@spam.nl (Koen) wrote in
<Xn*********************@194.109.133.20>:
The main cause is in my data structure, I think. Through time a
lot of sophisticated status info is introduced in my 'scoreboard'
forms and reports. These statusses are derived by all these nested
queries. I think I have to think about a complete rebuild of the
project...

Just a few more questions about subqueries, joins and unions, how
many table handles are used in case of:

a. A query with three queries on the same table, combined in one
query with UNION?
Four table handles, minimum.
b. A query on one table with a JOIN to the same table?
Three.
c. A query on one table with a subquery to the same table?
Three.

To get these numbers, count the number of tables or queries in the
FROM clause and add 1 for the query itself.
I hope anyone knows! Strange this is, I have a lot of Access
related books, but none of them covers this issue...


I found that not using saved querydefs made a big difference on
this issue (remember, if you've read my posts on this subject, I
encountered the problem before Access SR1, and so I was struggling
under a limitation of 1024 table handles in a replicated
database!).

I had originally built the app where I encountered the problem with
the philosophy of re-use of stored queries, and had a set of them
that I used as sources for many other queries. What I found was
that where it was possible, avoiding this nesting of queries not
only ameliorated the table handles problem, but also made my
queries faster. The latter is probably because my replacements were
more efficient, as they were not using reusable parts that were
multi-purpose, but only exactly what was needed.

Keep in mind that the last time this subject came up in the
newsgroup, the original poster determined that it really was a
database problem, and not a table handles problem. I don't remember
the context or when it happened or anything else, but did mark in
my mind that it was important to point out that the table handles
issue may not be relevant to every incidence of this error message.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.