Access 2007 only shows SQL in query

[ic_fish via AccessMonster.com]

Hi,
Am not a programmer, but am able to use pretty much all of Access 2000
functions and know what's what.

So - Access 2007 !!! Under certain circumstances, if a field is deleted in a
table, when I go to query, the pop up box will say "xxx Access could not
find" etc. Yep, fair enough. In 2000, you would go into query and the deleted
field would be easy to spot (no name) etc. Where as in 2007, it always
default to SQL which I cannot understand.

On one occasion, I was able to delete the field in SQL, only then did access
2007 bring up the query view and I was able to proceed.

When I press the design view in 2007, or try to de-select SQL, it just
doesn't do it !!!!

Any tips?

Ian

 

[Allen Browne]

In Access 2003 and earlier, if you opened a query that referred to table
that was no longer in the database, the query opened in design view and
modified the SQL so it matched what you saw on screen. Consequenty your
carefully prepared and tested query statement just disintegrated. It
happened so easily, e.g. where an attached table was no longer present.
People were losing hours of work.

In A2007, MS changed this so that it leaves the SQL statement intact.
Naturally, it cannot display design view for a table that it has no idea
about: what fields should it display? Andy what data type are they?

So, instead of losing your query, you now have options about what to do,
e.g.:
a) reattach the table, so the query can be shown in design view;
b) temporarily put the missing stuff back in, so it can be shown in design
view;
c) edit the SQL statement yourself, to take out the now spurious names.

Those choices are *much* better than the previous versions' approach of
destroying the stuff in the SQL statement that could not be shown in design
view.