473,231 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

"Cannot open any more databases": strategy needed

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
4 3245
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Ryan Park | last post by:
Hi, //SITUATION I got a panel control that hold a certain position on a form. Every controls or UIs are on this panel. At certain situation, I called dispose() method of this panel control...
1
by: Niranjan | last post by:
I have a relatively straight forward report. The query that populates data for this report is complicated. The report opens without any problem and I can print it as well. However, If I have...
0
by: Mr.KisS | last post by:
Hello. I'm under Windows XP PRO SP1, IIS 5.1 ans SQL SERVER 2005 Express. When i try to open a connexion with : <connectionStrings> <add name="AppCnxStr"...
2
by: Mr.KisS | last post by:
Hello. I'm under Windows XP PRO SP1, IIS 5.1 ans SQL SERVER 2005 Express. When i try to open a connexion with : <connectionStrings> <add name="AppCnxStr"...
1
by: Shilpa | last post by:
Hi All, I need C# code needed to open the windows "Open with" dialog Regards, Shilpa
0
by: Holly Cross | last post by:
I have a report that uses several subreports. The report looks fine in print preview, but when I send it to the printer, I keep getting this message "Cannot open any more databases." This happened...
7
by: Mathew Butler | last post by:
I'm investigating an issue I have when pulling data back from MS access I'm retrieving all rows from a column in a table of 5000 rows - accessing a column of type "memo" ( can be 65353 character...
3
by: Joanne | last post by:
I have searched through previous threads regarding this error. I went back through all my code to make sure I closed and set to nothing all open db's and rs's. However, I still have one computer...
3
scubak1w1
by: scubak1w1 | last post by:
Hello, Did a search here, manuals and Googled, etc generally, so apologies if I did not find the answer to my issue - that is, I try and 'self help' as mush as I can... I adjusted my...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.