ACC2002: System resource exceeded (3035)

[Patrick]

We encounter a problem with a query something like this.

SELECT TableA.Field1,
TableB.Field2,
TableC.Field3,
TableD.Field4,
FROM TableA,TableB,TableC,TableD

looks like full outer join (?).
We run this query in Acc97 without any probs. The result
was one record. This query is just collection information
form different tables and queries.
After we converted this .mdb to AccessXP the run of the
exact same query results with a system resource exceede.
The machine where it is running on is still the same. Even
installing Jet 4.0 Service Pack 7 did not improve.
I am assuming that Access temporarely allocate table or
memory space for the full outer join?
Any clue, hints to this problem? Registry mods?
thx
Patrick

 

[John Viescas]

No, this is not a Full Outer Join. It's a Cartesian Product. You say there
was only one row output - which must mean that all four tables each have
only one row, right? This should work in 2002. I can visualize a problem
only if all the tables contain thousands of rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Patrick]

Hi John
Okey, this Cartesian Product produces only one output.
Behind so-called tables are in fact other select-queries.
Executing those select-queries individually is producing
one record each.
Funny is, if I remove one "table" from this cartesian
product - does no matter which one - the query runs
without probs. having four or more it fails and
says "System resource exceeded". Neither in MS-Help, Tech-
net nor MSKB has a description for this message. And it
has run well on Access 97 and still it does.
So???? Wait for the next bug-fix? Should I contact MS
directly?
Do you want me to send this Acc2002 .mdb to you John?

Regards, Patrick

 

[John Viescas]

See my email reply... <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[John Viescas]

Well, I looked at your query, and it's a nightmare. It probably works in
A97 because A97 has a much smaller "footprint" in memory than does 2002.
I've seen a number of cases where a very very complex query runs in 97 but
fails in a later release.

Your query is a Cartesian Product of four other queries. But that's not
all - each of those queries is a query on query on query on query. EEEEP!

For example, "QueryA" is based on another query that contains two tables and
four other queries - and those four queries contain one or two tables
(usually the same ones) and other queries. Just following the "A" chain, I
found at least 30 tables or queries used as the source for the queries -
some of which were used more than once.

The bottom line is this is a horrendous query. You need to understand the
problem it is trying to solve and rewrite it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411