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 said:
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 said:
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)
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
.
