Fix Query too Complex error in Access

[Guest]

I have built a very feature-rich manufacturing system in Access. The business
rules are complex and so the queries are too. As I extend this application,
the "Query too Complex "error is coming up more frequently. I understand
this is due to the limits of memory handles. I had previously split the data
portion of the database from the programming. I've heard each linked table
increases the number of handles. I've since reintegrated the databases, which
has helped, although the DB is now much larger. I also have used temp tables
to store temporary data in a long set of queries. This has also helped but it
further complicates an already complex application. I request Microsoft do
whatever it has to do prevent this error from happening in Access.

thanks,
Doug Krueger

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ae130aaa24&dg=microsoft.public.access.queries

 

[David Lloyd]

Doug:

My own experience is that this error message can be generated for a number
of reasons. I have had queries that were running fine, that suddenly
started generating this error message. After some investigation, I found
that my client's data had changed in some subtle ways (in general, null
values in fields). Even though I had filtered out this "bad" data in the
queries used as the basis for the query generating the error, Access was
still aware of this data in the underlying table. After restructuring my
queries to look for nulls (even though I had filtered them out), I was able
to get the queries working again.

While I do not know all the possible sources of this error, in general, I
have found that restructuring your queries can eliminate this error in many
cases. Others may have additional/other points of view.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have built a very feature-rich manufacturing system in Access. The
business
rules are complex and so the queries are too. As I extend this application,
the "Query too Complex "error is coming up more frequently. I understand
this is due to the limits of memory handles. I had previously split the data
portion of the database from the programming. I've heard each linked table
increases the number of handles. I've since reintegrated the databases,
which
has helped, although the DB is now much larger. I also have used temp tables
to store temporary data in a long set of queries. This has also helped but
it
further complicates an already complex application. I request Microsoft do
whatever it has to do prevent this error from happening in Access.

thanks,
Doug Krueger

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ae130aaa24&dg=microsoft.public.access.queries