Because Access use the T-model when accessing the data trough ODBC Linked
Tables: first, it reads the value of the primary key for tens rows then it
uses these primary keys to read the rest of the values for the other columns
of each table. When you take a look with the SQL-Server Profiler and make a
comparaison between these two, it's easy to see that there is a lot more
traffic and requests made by Access when accessing the SQL-Server with ODBC
Linked Tables than with ADP.
When you're on a LAN, this added traffic and requests might be seen as not
that much important but if you have to go through the WAN, it't practically
to do as a sufficient speed without using Terminal Server; which add $$$ to
the overall costs. With an ADP project, I can easily go through the WAN at
practically the same speed as Terminal Server but without its cost.
Furthermore, even when you are on a LAN, the added requests still have their
impact on the SQL-Server itself: the more work you ask from it, the fastest
it will come to its saturation level as the number of concurrents users is
rising and you will either have to buy a more powerful machine or have a
less pleasant experience for the users.
I suppose that people who are saying that ODBC Linked tables are as good as
ADP are simply people who have never took a single second of their time to
take a look with the SQL-Server Profiler in order to see what's really going
on under the hood.
Part of the above problem can be relieve by using passthrough queries but
there are read-only and cannot be used for sub-reports; two problems that
you don't have with ADP. So saying that you can use the same stored
procedures with an ODBC project than with ADP is not really true.
Of course, there is this problem that we don't really know about the future
of ADP and of ADO but that's a moot point. It's clear that in a few
versions, MS will possibly throw ADO out to the garbage and keep ADO.NET
exclusively but when they will do so, they will quite probably incorporate
the equivalent functionality of ADP directly into what will be then the new
version of Access; whatever the technology that will be in use at this
moment.
In the meantime, I think that is a better idea to build an interface to a
database based on stored procedures than on ODBC linked tables.
Finally, personally, the moment that I stopped using ODBC Linked tables many
years ago was not even about performance or reports; it was simply because I
was becoming sick of seeing Access making p** into my hands everytime I
wanted to create any query that was not very simple; like a combination of
left joins with some subqueries or unions. When you work against a JET
database, the query engine is passable but when you have ODBC Linked tables;
it is simply too buggy for my taste.
And yes, I have worked on many projects with Access and SQL-Server, with
both ODBC Linked Tables or ADP and with or without Terminal Server; so I
know from personal experience the two sides of the medal.
--
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)
