If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL View
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 |
#2
|
|||
|
|||
SQL View
Marsh wrote:
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 -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#3
|
|||
|
|||
SQL View
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. -- Daryl S "Marsh" wrote: 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 |
#4
|
|||
|
|||
SQL View
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" wrote: 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. -- Daryl S "Marsh" wrote: 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 |
#5
|
|||
|
|||
SQL View
We'll need to see an example.
Marsh wrote: 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" wrote: 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. -- Daryl S "Marsh" wrote: 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 -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#6
|
|||
|
|||
SQL View
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. -- Daryl S "Marsh" wrote: 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" wrote: 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. -- Daryl S "Marsh" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|