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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)


"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in message
news:Xns9C03A9CD71017f99a49ed1d0c49c5bbb2@xxxxxxxxxxxxxxxx
"Tony Toews [MVP]" <ttoews@xxxxxxxxxxxxxxx> wrote in
news:8fn105hhea1h7jmg0pu55i7f0a0pitfqgj@xxxxxxx:

"Sylvain Lafontaine" <sylvainlafontaine2009@xxxxxxxx> 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/


.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... "remains with Jet and linked tables or remains with Jet ... but go with SQL pass-through queries and unbound forms" ... RecordSource is a query with a where clause that limits the number of rows ... > queries under MDB was bad and worst than the one offered by ADP while you ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Error 3420 Object invalid or no longer set under Vista
    ... Sometimes the JET optimizer does not run the subquery to completion. ... Problem is in the ORDER BY statement of the problem query below ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)
  • Re: Query runs long in one db, fast in another....
    ... One thing to note here is that if it is taking Jet more than a few ... seconds to process a query, then it is either time to use a bigger ... to break down the original query into smaller queries that Jet can ... same queries (with all the same redundant joins) wrapped in sql server ...
    (comp.databases.ms-access)
  • Re: Optional Parameter for Querydef with Default Built In
    ... > (from Jet Database Engine ProgrammersGuide) ... > "Parameter queries can only be implemented by using a stored query. ...
    (comp.databases.ms-access)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)