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

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

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


"Ivan" <ivan@nekje.si> wrote in message
news:ekVnaqXzJHA.5496@TK2MSFTNGP04.phx.gbl...
> 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
>

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)


"Ivan" <ivan@nekje.si> wrote in message
news:ejy%23Z6XzJHA.1372@TK2MSFTNGP05.phx.gbl...
> 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
>
>
> "Ivan" <ivan@nekje.si> wrote in message
> news:ekVnaqXzJHA.5496@TK2MSFTNGP04.phx.gbl...
>> 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
>>
>
>

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)


"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message
news:es1hORZzJHA.5496@TK2MSFTNGP04.phx.gbl...
> 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)
>
>
> "Ivan" <ivan@nekje.si> wrote in message
> news:ejy%23Z6XzJHA.1372@TK2MSFTNGP05.phx.gbl...
>> 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
>>
>>
>> "Ivan" <ivan@nekje.si> wrote in message
>> news:ekVnaqXzJHA.5496@TK2MSFTNGP04.phx.gbl...
>>> 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
>>>
>>
>>
>
>

"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote:

>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
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

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


"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in message
news:8fn105hhea1h7jmg0pu55i7f0a0pitfqgj@4ax.com...
> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote:
>
>>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
> --
> Tony Toews, Microsoft Access MVP
> Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
> Granite Fleet Manager http://www.granitefleet.com/

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)


"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in message
news:8fn105hhea1h7jmg0pu55i7f0a0pitfqgj@4ax.com...
> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote:
>
>>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
> --
> Tony Toews, Microsoft Access MVP
> Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
> Granite Fleet Manager http://www.granitefleet.com/

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)


"Ivan" <ivan@nekje.si> wrote in message
news:%23$j5VakzJHA.1492@TK2MSFTNGP04.phx.gbl...
>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
>
>
> "Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in message
> news:8fn105hhea1h7jmg0pu55i7f0a0pitfqgj@4ax.com...
>> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote:
>>
>>>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
>> --
>> Tony Toews, Microsoft Access MVP
>> Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
>> Granite Fleet Manager http://www.granitefleet.com/
>
>

"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in
news:8fn105hhea1h7jmg0pu55i7f0a0pitfqgj@4ax.com:

> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote:
>
>>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.

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Ivan" <ivan@nekje.si> wrote in
news:#$j5VakzJHA.1492@TK2MSFTNGP04.phx.gbl:

> 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/