I can most agree with the above. As mentioned, even when you do NOT use sql
server, ms-access does not (normally) pull the whole data over, but only
records required.

The same logic applies to sql server. It turns out that sql server is
actually more forgiving of doing really dumb stuff, but at the end of the
day, good designs are required to mim the network traffic regardless if you
are using a file share (mdb back end), or sql server...

If you are looking for confirmation that Jet being involved is a problem,
then you are doomed to disappointment. Jet is not a problem -- in fact, it
turned out to be more capable and more efficient than even its developers
suspected it would. But, you are correct: if the orginal implementation
included inefficient uses of Jet, they will still be there. And, yes,
because of the following, moving from multiuser to client-server often does,
initially, perform worse.

And, just as a well-designed, well-implemented single-user database does not
automatically result in a well-designed, well-implemented multiuser
database, neither does a well-designed, well-implemented multiuser database
linked to a server automatically result in a well-designed, well-implemented
client application to the server database. You need to understand and take
advantage of the strengths of each of these environments.

It does not, as so many detractors claim "pull the entire database across
the network" -- that just is not so. It treats the shared folder just as it
would a folder on the local hard drive... retrieving only what it needs to
do whatever is the task at hand. If the database application is
well-designed and well-implemented that may be only the index on which the
record is selected, and the particular record (or "page", depending on what
you specified) of interest.

And, while you can get some gains from stored procedures, and other SQL
Server-specific items, I have worked on a number of client applications to
server databases where those were, by client demand, avoided. It turns out
that Jet-ODBC-server is far more efficient than many would suspect -- you
can get very good performance without resorting to stored procedures, etc..
(triggers are often used to implement referential integrity on some server
databases, and may be created by the data modeling software used by the
DBA). Microsoft SQL Server is something of an exception, because you can
specify referential integrity in a manner much the same as you do in Access.
The reason the client had us avoid using server-specific features was
two-fold: (1) they wanted to preserve their options to change server
databases, and, (2) competent Access developers were easier to find and less
expensive to contract than competent server database DBAs and developers.

Larry Linson
Microsoft Access MVP