'system resource exceeded' and 'query too complex'

[AccessMan]

I have a query that involves 10 instances of the same query which are
daisy-chain-linked to each other via left joins. (I am trying to extract all
of the paths to the top of an assembly tree.) Apparently, the query is at
the edge of some restrictions that are unknown to me. If I try to pull in
another field from each of the base queries I get a 'system resource
exceeded' error. If instead I try to add another base query I get a 'query
too complex' error. Searching help did not help. Can anyone help me
understand what I am up against and/or help me find a way around this?

Thanks!

 

[Dale Fye]

What are you going to do with this info? I cannot imagine you actually
using fields from each of the levels of the heirarchy. However, I can
imagine needing to identify all of the parts, and the associated quantities,
in which case I would either use a recursive function to get all of the
"Parts".

I have a number of applications that use Parent/Child relationships within
the same table, and I cannot ever recall having a need to have a single
record that displays all of the possible nodes (elements) along this path.

I assume you are using Access as both the front and backend for this
application.

Dale

 

[Allen Browne]

I don't have a solution so hopefully others will reply as well.

These are very generic messages. "Too complex" occurs when Access doesn't
understand the query (e.g. bad brackets, inappropriate delimiters, ambiguous
data types, field/table names that are reserved words) as well as cases
where the query is beyond limits (too many ANDs in a WHERE clause, too many
UNIONs, nesting beyond 50 levels, ...)

"System resources" can be anything: a bad VBA function call, a bad driver
(e.g. video), too many database links, transaction space exceeded, not
enough temp space on the system drive, or calling a routine repeatedly that
fails to release memory (and there are some of those in Access itself.)

For this particular query, you have 10 instances of the same query.
Presumably it's something handling generations or bill-of-material data:
perhaps like the pedigree table in this article which has 15 instances of
the one table:
http://allenbrowne.com/ser-06.html

Is there anything that can be simplified here?
If the source query contains multiple tables, can you simplify it by
eliminating some?
If they are linked tables, does using a local table make a difference?
Have you included the table alias with all field names (e.g. those in the
WHERE or ORDER BY clause) so they are unambiguous?
Does the query contain any domain aggregate functions such as DLookup() -
there are known issues with these.
Are any field or table names or aliases reserved words:
http://allenbrowne.com/AppIssueBadWord.html

Presumably this problem arises at a particular point, e.g. you can get 9
instances of the one table, but the 10th one fails.

One of the problems with this kind of data is the possibility of infinite
recursion (e.g. where a record is its own grandfather.) What I have usually
done is to cheat and create a local temp table that resolves the data into a
non-normalized format (basically like the output of your query), populating
it with VBA code. The code must be able to resolve the data in a finite
number of steps (e.g. 10), and if it can't it reports the issues the user
must resolve brefore continuing. You can then use the temp table as the
source for your report (or whatever.)

If you want some alternative ways of handling this kind of data, thse
articles from Joe Celko may help:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

Hope something amongst that is useful.

 

[AccessMan]

This query meets my needs, when it is on the good side of the edge I
mentioned, and I can't see a way to simplify it significantly and still have
it provide what I need. Yes, it's all Access.

 

[AccessMan]

I don't have a solution so hopefully others will reply as well.

These are very generic messages. "Too complex" occurs when Access doesn't
understand the query (e.g. bad brackets, inappropriate delimiters, ambiguous
data types, field/table names that are reserved words) as well as cases
where the query is beyond limits (too many ANDs in a WHERE clause, too many
UNIONs, nesting beyond 50 levels, ...)

"System resources" can be anything: a bad VBA function call, a bad driver
(e.g. video), too many database links, transaction space exceeded, not
enough temp space on the system drive, or calling a routine repeatedly that
fails to release memory (and there are some of those in Access itself.)

For this particular query, you have 10 instances of the same query.
Presumably it's something handling generations or bill-of-material data:
perhaps like the pedigree table in this article which has 15 instances of
the one table:
http://allenbrowne.com/ser-06.html

Is there anything that can be simplified here?
If the source query contains multiple tables, can you simplify it by
eliminating some?
If they are linked tables, does using a local table make a difference?
Have you included the table alias with all field names (e.g. those in the
WHERE or ORDER BY clause) so they are unambiguous?
Does the query contain any domain aggregate functions such as DLookup() -
there are known issues with these.
Are any field or table names or aliases reserved words:
http://allenbrowne.com/AppIssueBadWord.html

Presumably this problem arises at a particular point, e.g. you can get 9
instances of the one table, but the 10th one fails.

One of the problems with this kind of data is the possibility of infinite
recursion (e.g. where a record is its own grandfather.) What I have usually
done is to cheat and create a local temp table that resolves the data into a
non-normalized format (basically like the output of your query), populating
it with VBA code. The code must be able to resolve the data in a finite
number of steps (e.g. 10), and if it can't it reports the issues the user
must resolve brefore continuing. You can then use the temp table as the
source for your report (or whatever.)

If you want some alternative ways of handling this kind of data, thse
articles from Joe Celko may help:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

Hope something amongst that is useful.