Table handles limited - why?

[MaxP]

Hello,

Our database (a access 2003 db split into front- and backend) produces the
error 3048 if i open too much (10) different forms at once. The application
is complex and uses lots of nested queries, the forms are also quite complex.
Every reference in the code is set to nothing before going out of scope, we
use a global handle to the CurrentDB, and all recordsets are closed when not
longer needed.

I searched the web for information on this topic, and all I found out was
that there is a limit to the table handles access can handle.

From all I red, there is no possibility to enlarge this limit, and the
common workarounds (using static tables for view results, reduce the
complexity of the forms,...) cannot be applied, because we really need to see
all of this information at once, and the information shown in the forms
belongs together, it makes no sense to close one form, open another, and then
go back to get the picture.

As I think there is no practicable solution other than migrating to
SqlServer with a new developed Client, there is one question left that would
be very interresting to me:
WHY IS there a limit for table handles? CPU and memory usage are always far
from their limits when this error occurs. Is this just a hardcoded number?
That doesn't make much sense from a developers view!

Btw: I also tried to put the Tables in a SqlExpress DB, which worked fine
until i tried to read the data from my access frontend. As SqlExpress can't
handle paralell usage, I got Odbc timeouts with no end.
Using exactly the same frontend and the same Tables in a SQLServer 2000
Standard Edition, everything worked well, only the Error 3048 was also there.

Please tell me that this is not just hardcoded, that would be like crippling
a healthy application for no reason!

 

[Arvin Meyer [MVP]]

A single query can have as many as 32 tables. I don't think that I ever used
even half that number and that was on a client's poorly designed database.
You might try building separate subqueries and joining them together. While
I can't guarantee that will work, it's your best shot, short of redesign.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[MaxP]

Our db is really normalized, which results in a lot of tables (59). As we
have the need for statistics and other large queries, we use views that join
smaller views, to keep it readable and fast. That should be what you
suggested.
All of this views run at a good performance, the problem doesn't occur when
even the biggest of them is executed.
It only happens when we have some of the bigger forms open at the same
moment. The forms are programmaticaly connected, and need to be shown at the
same time.
The performance of our app doesn't even slow down when we have 9 of these
forms open. But when I open just one more(in our case), suddenly that message
pops up. As stated above, CPU and memory usage are quite low at this time.
There must be an overall limit to table handles, as suggested by other
people (partly MVPs).
I am shure it is there, my question should be: Is there a way of getting
around that limit, or, if not, why would anyone build something like this
limitation in a database?

 

[MaxP]

Please, could anyone answer the question?

Our db is really normalized, which results in a lot of tables (59). As we
have the need for statistics and other large queries, we use views that join
smaller views, to keep it readable and fast. That should be what you
suggested.
All of this views run at a good performance, the problem doesn't occur when
even the biggest of them is executed.
It only happens when we have some of the bigger forms open at the same
moment. The forms are programmaticaly connected, and need to be shown at the
same time.
The performance of our app doesn't even slow down when we have 9 of these
forms open. But when I open just one more(in our case), suddenly that message
pops up. As stated above, CPU and memory usage are quite low at this time.
There must be an overall limit to table handles, as suggested by other
people (partly MVPs).
I am shure it is there, my question should be: Is there a way of getting
around that limit, or, if not, why would anyone build something like this
limitation in a database?

 

[Douglas J Steele]

Unfortunately, if you're running into a limit, I doubt very much that
there's any way around it. Most of the limits like that are physical
limitations.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[MaxP]

Thanks for your responses,

I think we pushed this app to the limits and should re-implement it with a
..Net client and SqlServer-Backend.
All other infos I found in the last days came to the same consens, only sad
thing about it is, that we where not aware of such limitations when the
development was started. I just wandet to be sure that there is no other way,
before I'm telling it to my boss.

regards,
Max Prasek