"System Resource Exceeded" - message when running query

[David Gibson]

Hi,

I would be grateful if anyone can hopefully point me in the direction
of a solution to the message "system resource exceeded" that I get
when running a query.

The query causing the message is at the top of a pyramidal tree
consisting of "sub" queries if thats the rigth way to put it.

The lowest level queries all run fine, then when they are joined via
union queries to the next level all runs fine.

When those union queries are then further unified to the last 2 union
queries the last two in the hierarchy both run fine.

Joining the last 2 to get the final query is where the message box
appears.

I had an original message "query too complex" but resolved that by
introducing another hierarchical level in order to split up the one
giving the "query to complex" message.

Have I reached the limitation of access or is there some patch or
upgrade to the database engine that I can use to resolve the problem.

I am using access 2002 on a winXP machine with 768mb ram & a 1.3GHz
athlon processor.

Regards
Dave Gibson

 

[GreySky]

I would tend to believe you've hit a system barrier.

If possible, append some of results of your queries to
tables, then union those tables (instead of queries).
This is probably the best way to light that load.

You probably would only have to introduce this once. Pick
an appropriate spot, say, half-way through, and have your
union query append data to a base table. Then use that
base table to union with the remainder of your process.

It's rarely black-and-white on these issues. Even if you
manage to make your query work on your system, I'd suggest
it might be suspect when working on another system.

It's a judgement call here. Split up the process is my
recommendation.

David Atkins, MCP

 

[David Gibson]

Thanks David,

I opted for the "make table" route on the last two queries before the
top of the pyramid by getting each one to make a table. I then unified
the two tables & its all OK.

The only drawback is the table data is static & not dynamic. Are you
aware if there is any procedure to get the 2 x tables to auto refresh
when there is a call on the union query linking the 2 x tables. That
would avoid absent mindedness not remaking the tables each time.

Regards
Dave Gibson

 

[GreySky]

I agree the "auto-refresh" problem is an irritating one.
Unfortunately, there isn't much one can do other than
manually handle that.

I personally wouldn't use make-table queries, for the
simple fact that make-table queries will cause your
database filesize to grow an an inordinate rate. Compared
with the delete/append option, there's a very significant
difference over time.

Good luck.

David