Access Pipeline to SQL Server and inefficiencies

[Guest]

Can anyone provide me a link or an explanation as to why SQL Server does not
run optimized SQL coming over from Access???

I know there might be some politics involved...Microsoft does NOT want you
to be able to access SQL Servers tables easily in Microsoft Access...

Any information would be greatly apprecaited.

Thanks in advance.

wnfisba

 

[Danny J. Lesandrini]

Can't you pass optimizer hints with a Pass Through query? Or does ODBC get in the way?

 

[Guest]

How can I pass Optimizer hints through a pass-through query??? We actually
had our DBA monitor the Access query coming over to SQL Server and it always
seems to performs table space scans and never utilizes indexes. The query and
joins on the Access side have been torn apart and are efficient. It just
seems to be what's coming over the pipeline.

 

[Douglas J Steele]

When you say it always seems to perform table space scans, are you talking
about pass-through queries, or are you using linked tables? Are the queries
being used in reports?

 

[Danny J. Lesandrini]

Create a new query but before adding any tables, select Pass Through
from the Query | SQL Specific menu.

Once opened, paste or type your query, with hints. Next, choose Properties
from the View menu and set the ODBC property to point to your SQL Server.
(Either type in the ODBC string or click the elipsis to navigate to a DSN.)

Run the query.

I have a pass-through query to return the Server and DBS name.

select @@ServerName As ServerName, DB_NAME() AS DatabaseName

As you know, this won't work from an Access query, but it works fine in a
Pass Through query.

 

[Albert D.Kallal]

I know there might be some politics involved...Microsoft does NOT want you
to be able to access SQL Servers tables easily in Microsoft Access...

not true at all. In fact, they seem to spend all kinds of time and ways to
get people to upgrade to use sql server!!!

They LOVE when this happens..and tend to do handstands to make this happen.

Two things:
1# if you create a ADP (ms-access project), then ALL OF YOUR queries and
sql is executed 100% native server side. There is no JET involved, and you
don't even have local tables. This is a 100% oleDB native sql server
connection. This is not even odbc. So, ms-access has some serious
development dollars spent on its ability to work with sql server in a native
mode. All sql used in this fashion is compliry optimized by sql server
before any results are retuned to the ms-access client. I only recommend
using access "projects" for new applications, as exiting ones can't use dao
code you write, and extensive modifications are required. however, you are
JUST using ms-access to write sql queries, and build reports, and your data
is in sql server, then a adp project is great choice...as it takes less work
to get better performance then linked odbc tables.

#2
If you are using odbc linked tables, then the simple solution in most cases
for a report is to create a view on the server side, and link to that.

Furthermore, a few other posters here suggested that you can use a
pass-through query..and again, it will be 100% optimized on the server
side.....

Between good designs, using linked views, and pass-through quires...you
ms-access application will run as well as c++, or vb, or whatever favorite
developer tool you use to build a user interface to sql server. Don't blame
ms-access...as it is no worse then VB, or anything else if you do it
right....

 

[Guest]

I am indeed talking about linked SQL Server Tables. Part of the politics I'm
dealing with on this end is that end users do NOT want to lose that control
over accessing and massaging returned data from SQL Server. When you start
talking to them about "Stored Procedures" or "Views", then they realize that
that implies IT dependency which is a hard pill for them to swallow.

We recently had a software application upgrade where the vendor of our app
cahnged a lot of columns from char to varchar. That being done has created
LOTS more rows on pages and thus a lot more contention for pages; which is
why we believe their Access Database has become real slow.

It certainly sounds as though Stored Procedures and/or Views is the simple
solution here. So now I am wondering if anyone has ever encountered something
like this and have counter attacked with a more viable, still independent
solution.

Thanks everybody!

 

[Douglas J. Steele]

A pass-through query doesn't have to be a stored procedure or a view. It's
can be simply a query that's run on the server, rather than on the client.

Create the query, then select SQL Specific from the Query menu. Define the
connection string, and try it.