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

Discussion in 'Microsoft Access Queries' started by Ivan, May 5, 2009.

  1. Ivan

    Ivan Guest

    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, May 5, 2009
    #1
    1. Advertisements

  2. Ivan

    Ivan Guest

    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, May 5, 2009
    #2
    1. Advertisements

  3. 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, May 5, 2009
    #3
  4. 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, May 5, 2009
    #4
  5. 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 [MVP], May 6, 2009
    #5
  6. Ivan

    Ivan Guest

    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
     
    Ivan, May 6, 2009
    #6
  7. 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, May 6, 2009
    #7
  8. 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)
     
    Sylvain Lafontaine, May 6, 2009
    #8
  9. 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, May 6, 2009
    #9
  10. 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, May 6, 2009
    #10
  11. 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)
     
    Sylvain Lafontaine, May 6, 2009
    #11
  12. Ivan

    Ivan Guest

    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, May 7, 2009
    #12
  13. Ivan

    Ivan Guest

    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
     
    Ivan, May 7, 2009
    #13
  14. 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
     
    Tony Toews [MVP], May 7, 2009
    #14
  15. Ivan

    Spiracle

    Joined:
    May 7, 2009
    Likes Received:
    0
    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
     
    Spiracle, May 7, 2009
    #15
  16. 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.
    Probably only because it caused the query to be recompiled on the
    next save.
     
    David W. Fenton, May 7, 2009
    #16
  17. 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.
     
    David W. Fenton, May 7, 2009
    #17
  18. Ivan

    Spiracle

    Joined:
    May 7, 2009
    Likes Received:
    0
    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.
     
    Spiracle, May 8, 2009
    #18
  19. Ivan

    MkJones

    Joined:
    May 13, 2009
    Likes Received:
    0
    I had similar symptoms to the ones you describe after SP2, but only on 'UNION ALL' queries. Removing the 'ALL' stopped the errors.
     
    MkJones, May 13, 2009
    #19
  20. Ivan

    vaninfranc

    Joined:
    May 27, 2009
    Likes Received:
    0
    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....
     
    vaninfranc, May 27, 2009
    #20
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.