Steve,
If you use pass-through, not only can you not
pass dynamic parameters and not take advantage of a cached query plan,
but
Access can't let you edit the resulting recordset through a form<<
I assumed here that this was a read-only, reporting style application
so there would be no editing of records. I assumed this because users
who don't have rights to create objects on a database tend not to have
write permissions either, but perhaps I assumed wrong.
Why would you do a silly thing like that?<<
Passing through a call to a stored procedure with parameters does
indeed make use of the stored procedure execution plan. For example,
if my pass through query SQL is
exec prMyProcedure 1
and I change it programatically to
exec prMyProcedure 2
SQL server will run the procedure with the parameter 2 rather than 1,
using the current execution plan. The work will be done on the server
and only the resultset (hopefully tiny) will be passed to the
application.
If instead my pass through query SQL was a SELECT with parameters in my
WHERE clause tailored programatically, then yes, I would have no saved
execution plan to run against so this would not be as efficient, but I
would still do my processing on the server. Of course, I would only
use this approach if I coudn't use stored procedures due to some kind
of restriction. If you use linked tables, you will need to be very
careful in designing your queries or you risk dragging tables across
the connection for Jet to process locally. Will a SELECT run from MS
Jet with linked tables will be more efficient that an equivalent SELECT
run directly on SQL Server via pass through? I doubt it but I suppose
it could be possible in certain cases. However, I can't imagine that
your SELECT with Jet/linked tables would ever be more efficient than
running a stored procedure via pass through.
I've had the unenviable task of cleaning up many reporting applications
designed in MS Access using linked tables to a SQL Server. I was able
to take reports that literally took an hour or hours to run and get
them to run in seconds by dropping all of the linked tables and
replacing all of the JET queries with pass throughs to stored
procedures. If you're not doing this then I would say that you're not
taking full advantage of SQL Server.
Bill