SQL View

[Marsh]

I inherited a database with a lot of queries, some of which need to be
modified. Many of them will not open in design view, only in SQL view. This
is not a behavior I have seen before. The DB was Access 2003, and I
converted it to Access 2007.
I am not that familiar with SQL to be able to really work in it.
Please advise on what may be causing this and how I can get to design view.
Thank you
Marsh

 

[Bob Barrows]

I inherited a database with a lot of queries, some of which need to be
modified. Many of them will not open in design view, only in SQL
view. This is not a behavior I have seen before. The DB was Access
2003, and I converted it to Access 2007.
I am not that familiar with SQL to be able to really work in it.
Please advise on what may be causing this and how I can get to design
view. Thank you
Marsh

There are several types of sql statements that cannot be represented in
Design View. Here are a few examples (this is not intended to be
an-all-inclusive list):
1. non-equi joins - these are joins that use comparison operators other than
"=". For example:
FROM table1 join table2 on tablea.field > table2.field
2. joins requiring a datatype conversion (or other VBA method) to make the,
work:
FROM table1 join table2 on tablea.stingfield = CStr(table2.numericfield)
3. Union Queries
4. Passthrough queries

 

[Daryl S]

Marsh -

Some queries cannot be viewed in design mode (e.g. UNION queries and
PASS-THRU queries). You don't say that the queries aren't working - if there
were syntax issues, such as a table name spelled wrong, then Access cannot
display that in design mode, so the query would need to be fixed in SQL View
mode first. You did not say that the queries aren't working, so I suspect
the former rather than the latter.

 

[Marsh]

Thank your for responding. I apologize for not being specific about these
queries.
They fall into three categories, select queries, make table queries, and
update queries. There are nine tables, three are linked to a SQL database.
There are several simple select queries that will go into design view, but
the action queries will not as well as several select queries.

 

[Bob Barrows]

We'll need to see an example.

Thank your for responding. I apologize for not being specific about
these queries.
They fall into three categories, select queries, make table queries,
and
update queries. There are nine tables, three are linked to a SQL
database. There are several simple select queries that will go into
design view, but
the action queries will not as well as several select queries.

 

[Daryl S]

Marsh -

Post the SQL View for one of the queries that won't go into design mode.
Also let us know if you get an error message when you try to go into design
mode. Do the queries run without problems?

As for modifying them, you may need to do that in SQL View for some types of
queries. If the modifications are due to SQL table changes, then that could
be why you cannot view them - check the table names and field names against
your SQL tables.