Why does a working query break when opened in design view?

  • Thread starter Thread starter Richard Grossman
  • Start date Start date
R

Richard Grossman

Anyone seen this behavior:

Working query - in production for months. User asks for a change, so I
open it, but even if I save it without making any changes, it no longer
works.

I can see the specific error - and I can start working on the tedious
task of reconstructing this query from scratch to see where the problem
is - but I am curious:

What changes when I edit and save a query without making any changes?

Using Access 2003...
 
FYI, apparently Access no longer "likes" subqueries in hard brackets.
Changing the hard brackets "[]" to parens "()" cured the problem.

But why it was fine before and not now...

I'm going to guess that it's a change that was in the MDAC update, which
I *think* was automatically installed as part of Windows Update.
 
It one of those weird things when Access tries to convert the info. on the
Query Design Grid to SQL String. Normally, SubQueries are enclosed in
parentheses but Access conversion uses square brackets and sometimes a
period after the closing square bracket.

The strange thing is that if you leave the SQL String / Query alone, the
square brackets work fine. However, if you open the Query in DesignView or
the SQL String in SQLView later, sometimes the square-bracket notation won't
work again (for this Query) and you need to edit the SQL String to use
parentheses as you have found out.
 
Back
Top