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
|
|||
|
|||
Case When statement in Access Frontend with Sql Backend
I am trying update my queries to perform better on my recently transferred
backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. Code:
'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5 END |
#2
|
|||
|
|||
Case When statement in Access Frontend with Sql Backend
If you are using ODBC tables for your access to the SQL-Server backend, then
you must not change anything to your queries and you must keep the regular Access/JET syntaxe. If this is a passthrough query or an ADP query, then it's wrong on many ways. As you didn't provide any detail on what you are doing exactly, it's impossible for me to tell you anything more on that because I won't start covering every possible combinations. Finally, you should take a look at the difference between multiposting and crossposting: http://www.blakjak.demon.co.uk/mul_crss.htm http://www.cs.tut.fi/~jkorpela/usenet/xpost.html -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "icccapital" wrote in message ... I am trying update my queries to perform better on my recently transferred backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. Code:
'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5 END I also had END AS AssetClassSort |
#3
|
|||
|
|||
Case When statement in Access Frontend with Sql Backend
Access SQL doesn't support Case When. You can use the syntax on SQL Server or
in a pass-through query. I'm not sure if the syntax is copied directly but the quotes around 'AssetClassSort' suggests you are attempting to assign a number to a string/text value. -- Duane Hookom Microsoft Access MVP "icccapital" wrote: I am trying update my queries to perform better on my recently transferred backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. Code:
'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5 END I also had END AS AssetClassSort |
#4
|
|||
|
|||
Case When statement in Access Frontend with Sql Backend
Well, as I stated in my post I am looking to transfer my backend database
(linked to access in the frontend) from access to SQL Server Express. After upsizing the database to a test database and trying to run some of the queries they were very slow, and so after some research I found that "optimizing" the queries was necessary (ie make them more transact sql queries) so that Access doesn't have to parse the query and make its own queries that is can then send on to SQL Server, which after setting up traceSQLMode and looking in sqlout.txt it turned out that my queries were being turned into 5 or 6. So that is the reason. I have read on passthrough queries, but articles are vague on issues with passthrough queries as recordsource for a report. I hope that helps to clarify my issue. Please clarify why "If this is a passthrough query or an ADP query, then it's wrong on many ways." And ask any leading questions that will help to clarify my issues. thanks. "Sylvain Lafontaine" wrote: If you are using ODBC tables for your access to the SQL-Server backend, then you must not change anything to your queries and you must keep the regular Access/JET syntaxe. If this is a passthrough query or an ADP query, then it's wrong on many ways. As you didn't provide any detail on what you are doing exactly, it's impossible for me to tell you anything more on that because I won't start covering every possible combinations. Finally, you should take a look at the difference between multiposting and crossposting: http://www.blakjak.demon.co.uk/mul_crss.htm http://www.cs.tut.fi/~jkorpela/usenet/xpost.html -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "icccapital" wrote in message ... I am trying update my queries to perform better on my recently transferred backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. Code:
'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5 END I also had END AS AssetClassSort . |
#5
|
|||
|
|||
Case When statement in Access Frontend with Sql Backend
Thanks Duane,
That was a misunderstanding of syntax on my part for the case when. I had originally CASE......END AS AssetClassSort but changed it when things weren't working and I forgot to change it back. AssetClassSort is just supposed to be the field name. "Duane Hookom" wrote in message ... Access SQL doesn't support Case When. You can use the syntax on SQL Server or in a pass-through query. I'm not sure if the syntax is copied directly but the quotes around 'AssetClassSort' suggests you are attempting to assign a number to a string/text value. -- Duane Hookom Microsoft Access MVP "icccapital" wrote: I am trying update my queries to perform better on my recently transferred backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. Code:
'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5 END I also had END AS AssetClassSort |
#6
|
|||
|
|||
Case When statement in Access Frontend with Sql Backend
There are many ways of upsizing an Access database to SQL-Server and
querying it after that. Simply saying that you are querying a SQL-Server database that has been upsized tell us nothing on how you are accessing it right now and saying that you want to optimize them is not any more informative either. From your last post, it now appears that you are using ODBC Linked Tables. When using directly ODBC linked tables, you cannot do anything to "optimize" the queries created in Access. They are as they are, using the regular syntax of Access/JET and that's all. The only thing you can do is to create Views on SQL-Server and link to these views as if they are tables. This might bring some relief on the performance issue but don't expect anything sky-rocketing and even in these cases, the queries that you are building in Access remains the same; with the exception that you will replace some JOIN between two or more tables with a View. If you want to have your Views updatable, see http://support.microsoft.com/kb/q209123/ With passthrough queries, your queries are executed directly on SQL-Server; so in this case, yes, you must now use the regular syntax of SQL-Server; also known as T-SQL. However, passhtrough queries are read-only and while you can use them as the record source for a report, you cannot use them for sub-reports. The other options is to use unbound forms or ADP. With unbound forms, you manage yourself the whole process of querying/updating data by making call with DAO or ADO and filling up your forms with VBA code. With ADP, it's also a whole new story; completely different from the other options. See the ADP newsgroup for more details. If you want to follow what happens between Access and SQL-Server, using the SQL-Server Profiler is a better way then with the traceSQLMode of ODBC. With the later, there are many things in the tracout that I never been able to figure out. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "icccapital" wrote in message ... Well, as I stated in my post I am looking to transfer my backend database (linked to access in the frontend) from access to SQL Server Express. After upsizing the database to a test database and trying to run some of the queries they were very slow, and so after some research I found that "optimizing" the queries was necessary (ie make them more transact sql queries) so that Access doesn't have to parse the query and make its own queries that is can then send on to SQL Server, which after setting up traceSQLMode and looking in sqlout.txt it turned out that my queries were being turned into 5 or 6. So that is the reason. I have read on passthrough queries, but articles are vague on issues with passthrough queries as recordsource for a report. I hope that helps to clarify my issue. Please clarify why "If this is a passthrough query or an ADP query, then it's wrong on many ways." And ask any leading questions that will help to clarify my issues. thanks. "Sylvain Lafontaine" wrote: If you are using ODBC tables for your access to the SQL-Server backend, then you must not change anything to your queries and you must keep the regular Access/JET syntaxe. If this is a passthrough query or an ADP query, then it's wrong on many ways. As you didn't provide any detail on what you are doing exactly, it's impossible for me to tell you anything more on that because I won't start covering every possible combinations. Finally, you should take a look at the difference between multiposting and crossposting: http://www.blakjak.demon.co.uk/mul_crss.htm http://www.cs.tut.fi/~jkorpela/usenet/xpost.html -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "icccapital" wrote in message ... I am trying update my queries to perform better on my recently transferred backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. Code:
'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5 END I also had END AS AssetClassSort . |
#7
|
|||
|
|||
Case When statement in Access Frontend with Sql Backend
Thank you very much Sylvain for those thoughts. There is a lot there to use
and consider as I work through the "remodel" of the sytem. "Sylvain Lafontaine" wrote in message ... There are many ways of upsizing an Access database to SQL-Server and querying it after that. Simply saying that you are querying a SQL-Server database that has been upsized tell us nothing on how you are accessing it right now and saying that you want to optimize them is not any more informative either. From your last post, it now appears that you are using ODBC Linked Tables. When using directly ODBC linked tables, you cannot do anything to "optimize" the queries created in Access. They are as they are, using the regular syntax of Access/JET and that's all. The only thing you can do is to create Views on SQL-Server and link to these views as if they are tables. This might bring some relief on the performance issue but don't expect anything sky-rocketing and even in these cases, the queries that you are building in Access remains the same; with the exception that you will replace some JOIN between two or more tables with a View. If you want to have your Views updatable, see http://support.microsoft.com/kb/q209123/ With passthrough queries, your queries are executed directly on SQL-Server; so in this case, yes, you must now use the regular syntax of SQL-Server; also known as T-SQL. However, passhtrough queries are read-only and while you can use them as the record source for a report, you cannot use them for sub-reports. The other options is to use unbound forms or ADP. With unbound forms, you manage yourself the whole process of querying/updating data by making call with DAO or ADO and filling up your forms with VBA code. With ADP, it's also a whole new story; completely different from the other options. See the ADP newsgroup for more details. If you want to follow what happens between Access and SQL-Server, using the SQL-Server Profiler is a better way then with the traceSQLMode of ODBC. With the later, there are many things in the tracout that I never been able to figure out. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "icccapital" wrote in message ... Well, as I stated in my post I am looking to transfer my backend database (linked to access in the frontend) from access to SQL Server Express. After upsizing the database to a test database and trying to run some of the queries they were very slow, and so after some research I found that "optimizing" the queries was necessary (ie make them more transact sql queries) so that Access doesn't have to parse the query and make its own queries that is can then send on to SQL Server, which after setting up traceSQLMode and looking in sqlout.txt it turned out that my queries were being turned into 5 or 6. So that is the reason. I have read on passthrough queries, but articles are vague on issues with passthrough queries as recordsource for a report. I hope that helps to clarify my issue. Please clarify why "If this is a passthrough query or an ADP query, then it's wrong on many ways." And ask any leading questions that will help to clarify my issues. thanks. "Sylvain Lafontaine" wrote: If you are using ODBC tables for your access to the SQL-Server backend, then you must not change anything to your queries and you must keep the regular Access/JET syntaxe. If this is a passthrough query or an ADP query, then it's wrong on many ways. As you didn't provide any detail on what you are doing exactly, it's impossible for me to tell you anything more on that because I won't start covering every possible combinations. Finally, you should take a look at the difference between multiposting and crossposting: http://www.blakjak.demon.co.uk/mul_crss.htm http://www.cs.tut.fi/~jkorpela/usenet/xpost.html -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "icccapital" wrote in message ... I am trying update my queries to perform better on my recently transferred backend from Access to Sql Server. So I need to change a switch statement in Access to a Case When statement. I have made the adjustement as shown below and I can't save it because Access says missing operator. I don't know if my syntax is wrong or if access doesn't like the case when statement. Thanks for the thoughts. Code:
'AssetClassSort' = CASE WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Cas' THEN 1 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Equ' THEN 2 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='ADR' THEN 3 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Fix' THEN 4 WHEN SUBSTRING([Appraisals]![AssetClass],1,3)='Oth' THEN 5 END I also had END AS AssetClassSort . |
Thread Tools | |
Display Modes | |
|
|