Query is too complex after install Access 2007 SP 2 !?

[Ivan]

Hi,

yesterday I have installed the SP2 of my Access 2007 and now I have the
version: Microsoft(R) Office Access (12.0.6423.1000) SP2 MSO
(12.0.6425.1000).

In my access aplications I often use saved queries which are based on other
saved (sub)queries and on linked tables.Tables are linked text files, linked
excel files, ODBC linked SQL tables and linked or inner access tables.

After the instalation of SP2 I have noticed that my pretty simple queries
dont't run anymore and I'm getting the error notice: "Query is too
complex".

Access Help gives me only a little bit more: 'Query is too complex. (Error
3360) The query is too complex. Reduce the number of fileds in the SELECT
clause or the number of subqueries or tables in the join."

I my query I'm using only a join of a linked text file and an union query of
two tables and there are all together 15 fields!?

If I start to build the same query from the elementary units I run into the
same error!

My colleague with the Access 2007 version but without the SP2 don't have any
problem running the same query!

What can I do???

Ivan

 

[Ivan]

In the meantime I found out that if I first make a table (with make table
query) from the union query and then in my last query I join this new table
instead of union query then I don't have any such error anymore.

The problem is therefore in union query! Why?

Ivan

 

[Sylvain Lafontaine]

Installing SP2 is a major change; so did you try to decompile your MDB or
ACCDB database file after upgrading to SP2? (Just a wild guess here).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

Or even better: create a new, blank database and import everything into it.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Tony Toews [MVP]]

Or even better: create a new, blank database and import everything into it.

I'd suggest that before decompiling in this particular case as a query
problem would (or should) not be affected by decompiling.

Tony

 

[Ivan]

I have first created a new blank database, then I have newly linked all text
(csv) files and all (ODBC) SQL server residented tables and then I have
imported all other tables, queries, forms, ... And I have compact and repair
the database too.

The final result is that I still get the "Query is to complex" error in my
query where a text linked table is joined to an union query and this union
query is a union of selection from three different SQL server tables.

If I substitute the union query with the table, which I made before from
the same union query, then I don't get any error.

I have made an aditional experimet. I have created a completly new database
and I have created liks to some tables and then I have created all queries
from start (not imported them), which generated problems to me. And in such
case it seems, that I don't have any such occurences anymore.

OK, but I have now a lot to do! :-( :-(

In the past it was possible to recompile the database. Is this still somehow
possible or is the only way "Compact and Repair"? The last method don't
save my problem.

Ivan

 

[Sylvain Lafontaine]

Oh, it was just a wild guess. Clearly, it appears that there is a problem
with SP2.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

First, as you are using Office 2007, are you using a MDB or an ACCDB
database file? Second, with ACCDB and Access 2007, there is a new ODBC
Driver for JET: the ACE driver. Did you try with this new one or with the
older ODBC driver that comes with Access 2003 and previous?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[David W. Fenton]

I'd suggest that before decompiling in this particular case as a
query problem would (or should) not be affected by decompiling.

The decompile switch is for VBA code, not for query compilation
plans. The latter are cleaned up by compacting the front end, which
marks all compilation plans for recompiling the next time the
queries are called. Then after the compact, open the query and save
it (which will recompile it). This will not always help. I've found
that sometimes you have to copy the SQL into a new query and save it
to get it to work.

 

[David W. Fenton]

The final result is that I still get the "Query is to complex"
error in my query where a text linked table is joined to an union
query and this union query is a union of selection from three
different SQL server tables.

What about joining to the text linked table in the individual SELECT
statements in your UNION? That would be that instead of joining the
text linked table to a saved UNION query, you'd be joining it to the
tables that are the source tables for your UNION.

 

[Sylvain Lafontaine]

To be honest, the recurrent and inconsistant problems (sometimes they work,
sometimes they don't and all this with no recognizable pattern) of JET with
queries using a mix of UNION, Sub-Query and Outer Join is one of the main
reasons that I stopped using JET for most of my work a few years ago. This
was especially true (but not necessarily) when working against ODBC Linked
tables.

Now that you speak about it, I remember that sometimes, simply adding a
semi-colon ";" (or removing?) at the end of the query could be sufficient to
make it work correctly on many occasions.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Ivan]

Database is the ACCDB and the driver is the ACE driver. And as I said
before, my coleague has the same same system (Access SP1) as I had before my
upgrade to Access 2007 SP2. Because our ACCDB database is on the one of our
net servers and we have the same rights it is interested, that she don't
meet these difficulties as I meet after upgrade.

For me it is evident that the source of the problem is in the SP2!

Ivan

 

[Ivan]

Compacting and "recompiling" (i.e. opening , maybe adding a ';' or removing
it from the end of SQL statement of query and saving the changed query)
don't affect on my case.

As I said before, my coleague has the same same system (Access SP1) as I had
before my upgrade to Access 2007 SP2. Because our ACCDB database is on the
one of our net servers and we have the same rights it is interested, that
she don't meet these difficulties as I meet after upgrade.

For me it is evident that the source of the problem is in the SP2!

Ivan

 

[Tony Toews [MVP]]

The final result is that I still get the "Query is to complex" error in my
query where a text linked table is joined to an union query and this union
query is a union of selection from three different SQL server tables.

I've never worked with a link to a text file. I've always imported
them into a table, ensured they were clean and then worked with them
as tables.

If you want Microsoft to fix this problem try coming up with a sample
MDB/ACCDB with a table or two in MDB format as well as a sample text
file. We can then pass this reproducible set of files to MS and they
will open a bug against it.

They will probably fix this but it will take at least a month if not
three before it's fixed. Note though that this is my best guess.

Tony

 

[Spiracle]

I have the same problem - SP2 has killed a number of the larger queries in our app, specifically as you say those drawing from a union of three or more sub-queries. Strangly simplifying these underlying queries by temporarily cutting out one or more of the tables in the union has no effect - the query is still 'too complex'.

Removing SP2 with MS's oarpman tool makes the problem go away, so we've rolled back while we try to fiind out what's going on. I've tried most of the possible solutions detailed in this thread with no success.

The one thing that I have found out is that applying the KB957262 patch (http://support.microsoft.com/?kbid=957262) causes the same problems even when running full (not runtime) Access reporting itself as being SP1. This makes me suspect that the roots of the problem are in data connectivity rather than the DB engine (my tables are MySQL ODBC) though that could be a red herring.

Further than that nothing else to report.

Phil

 

[David W. Fenton]

To be honest, the recurrent and inconsistant problems (sometimes
they work, sometimes they don't and all this with no recognizable
pattern) of JET with queries using a mix of UNION, Sub-Query and
Outer Join is one of the main reasons that I stopped using JET for
most of my work a few years ago. This was especially true (but
not necessarily) when working against ODBC Linked tables.

I find it iffy to be joining UNIONs to anything, to be honest, as
you lose indexing. Obviously in the present case (with a linked text
file) there's no indexing to be used, but I have no doubt that Jet
will optimize a series of non-UNION SQL statements with joins to a
linked text file differently than a UNION query joined to a text
file. I have found that when forced to work with UNION queries, one
should avoid all criteria on the UNION query itself. This usually
means writing your SQL on the fly (instead of using a saved UNION
query with all the results in it), since you want your criteria to
be on the individual SELECT statements that are UNIONed together
instead of on the UNION results itself.

UNIONs are a form of denormalization so I am afraid it doesn't
surprise me that you get results that are suboptimal if you do
certain things.

As to subqueries, the annoyance for me has always been the lack of
use of indexes in NOT IN () clauses. The NOT EXISTS () often does
support it, but I've honestly never been able to wrap my head around
what EXISTS means in the first place.

Now that you speak about it, I remember that sometimes, simply
adding a semi-colon ";" (or removing?) at the end of the query
could be sufficient to make it work correctly on many occasions.

Probably only because it caused the query to be recompiled on the
next save.

 

[David W. Fenton]

Compacting and "recompiling" (i.e. opening , maybe adding a ';' or
removing it from the end of SQL statement of query and saving the
changed query) don't affect on my case.

Your method for "recompiling" is actually a waste of time -- the
query is going to be recompiled the next time it is run even if you
don't edit it, because that's what a compact does -- it marks the
saved compilation to be discarded and recreated the next time the
query is run.

Have you recreated the query from scratch? I have seen compacting
that doesn't do the job, but copying the SQL into a new query does
the trick.

Also, making sure the table statistics are up-to-date is important,
which means compacting your Jet back end, or optimizing your SQL
Server tables. To get the benefit of this you may have to completely
recreate your linked tables, because certain metadata saved in the
table links is not refreshed when it ought to be.

 

[Spiracle]

I've been doing a little experimentation and I've found, slightly bizarrely, that appending select '*' from any one of the source tables to the front of a 'too complex' query allows it to run, i.e.

SELECT qryTooComplexUnion.field1, qryTooComplexUnion.field2, qryTooComplexUnion.field3, tblOther.field1 FROM qryTooComplexUnion INNER JOIN tblOther ON qryTooComplexUnion.field1=tblOther.field1;

doesn't work, but

SELECT qryTooComplexUnion.*, qryTooComplexUnion.field1, qryTooComplexUnion.field2, qryTooComplexUnion.field3 , tblOther.field1 FROM qryTooComplexUnion INNER JOIN tblOther ON qryTooComplexUnion.field1=tblOther.field1;

does.

This is strange as I seem to be adding complexity.

 

[MkJones]

I had similar symptoms to the ones you describe after SP2, but only on 'UNION ALL' queries. Removing the 'ALL' stopped the errors.

 

[vaninfranc]

I had similar symptoms to the ones you describe after SP2, at the moment I haven't found any solutions or better, I have uninstalled sp2 with oarpman program. I have also contacted Access team, by they say they haven't made any modification at union query....."luckely" I not the only one with this problem....