Access Front - Sql Back End

[BillD]

I transferred the Tables and queries from an Access database to SQL Server
2005 Express database called db1. The Tables in the Access Front End are all
linked to the SQL Server database(Linked ODBC). All the Queries in the Access
Front End do not show any link to the SQL Server database. I know that all of
the Queries are in the SQL Server Database db1. Some of them are marked with
a red X. I presume that these queries did not convert properly. The SQL
Server Database id on my desktop PC and the Access Front End is on the Same
PC.
Question: When I run a Report that uses a query, is the query preformed in
the SQL database or in the Access.mdb Front End? Is the processing being done
in the SQL Server database and then sent to Access for viewing? If the
Access Front End were on a different PC, would the processing be done on the
Server or on my Desktop PC? Would there be a major difference in processing
time?

Bill D

 

[Pat Hartman]

Before you go too much further with this, redo the conversion and this time,
name your database and choose the linked tables option. Access projects are
being deprecated and will no longer be enhanced with new functionality.
They will run under A2007 but do not support any of the new feature set.
With the linked tables option, your application will require minimal or even
no changes to run.

When the back end database is SQL Server, DB2, Oracle, or any other RDBMS,
query processing happens at the server and the result set is returned to the
client. It is IMPERATIVE, that your queries have selection criteria that
limit the rows and columns returned. If you treat server tables as if they
were Jet tables, you will get NO benefits from switching to a database
server back end.

Access makes every attempt to "pass-through" queries to be processed on the
server. Some things interfere with this like the use of user defined
functions. Search for the KB library article on optimizing Access for
client/server to get an idea of the kinds of application changes you might
want to make.

 

[BillD]

If I understand you correctly, when I run a query from the Access front end
that the processing will be done on the SQL Server Database. Is this correct?
I converted the queries to the SQL Server as well as the tables. The queries
are showing on the Front End Access Database and on the SQL Server Database.
There is not an arrow showing a link. What is with this setup?

Thanks
Bill Depow

 

[Pat Hartman]

I don't know since I don't use projects. Yes - Jet sends the queries to the
server for processing - BUT - you need to understand the things that will
PREVENT Jet from doing that. If the query contains some element that cannot
be translated to T-SQL, Jet will request that the server send all the data
from all the tables in the query and do the processing locally. This is
what you want to avoid at all costs so get the paper I referred to and read
it.

 

[Barry Andrew Hall]

Dont forget in access, when you make a new query, press the "query" option
at the top and choose "Sql Specific" then "pass through"... write your t-sql
here. When you call this query you bypass jet altogether and it just runs on
the server. Really useful, these have helped me a lot over the years.

hth

Barry

 

[Armen Stein]

Dont forget in access, when you make a new query, press the "query" option
at the top and choose "Sql Specific" then "pass through"... write your t-sql
here. When you call this query you bypass jet altogether and it just runs on
the server. Really useful, these have helped me a lot over the years.

hth

Barry

Hi Barry,

What Pat is saying is that *simple* Access queries may be passed to
SQL Server for processing, even if they are not passthrough queries.
So you don't *always* need to use passthrough queries for performance.
Sometime the easy way using linked tables will work fine.

If you have a query that's performing poorly, you can test it in
Management Studio against the database directly to see if it will run
faster. Sometimes the difference is dramatic. If that's the case,
then as you say a passthrough will really help.

Also, Jet will often process subqueries and simple calculations
locally. In presentations I sometimes demonstrate the difference
between the exact same subquery using linked tables vs passthrough.
The linked tables take about 20 secs, the passthrough is almost
instantaneous.

Here's an example to show this using Northwind:

SELECT *
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT TOP 10 PERCENT
Customers.CustomerID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Customers.CustomerID
ORDER BY Sum([Order Details].[UnitPrice]*[Quantity]*[Discount])
DESC)));

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[BillD]

Thanks for all the helpful tips. I tried to respond earlier but Explorer or
Site was not co-operating.

Dont forget in access, when you make a new query, press the "query" option
at the top and choose "Sql Specific" then "pass through"... write your t-sql
here. When you call this query you bypass jet altogether and it just runs on
the server. Really useful, these have helped me a lot over the years.

hth

Barry

Hi Barry,

What Pat is saying is that *simple* Access queries may be passed to
SQL Server for processing, even if they are not passthrough queries.
So you don't *always* need to use passthrough queries for performance.
Sometime the easy way using linked tables will work fine.

If you have a query that's performing poorly, you can test it in
Management Studio against the database directly to see if it will run
faster. Sometimes the difference is dramatic. If that's the case,
then as you say a passthrough will really help.

Also, Jet will often process subqueries and simple calculations
locally. In presentations I sometimes demonstrate the difference
between the exact same subquery using linked tables vs passthrough.
The linked tables take about 20 secs, the passthrough is almost
instantaneous.

Here's an example to show this using Northwind:

SELECT *
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT TOP 10 PERCENT
Customers.CustomerID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Customers.CustomerID
ORDER BY Sum([Order Details].[UnitPrice]*[Quantity]*[Discount])
DESC)));

Armen Stein
Microsoft Access MVP
www.JStreetTech.com