AP wrote:
Thanks, are there any performance issues with simply bringing the SQL
tables in as linked table rather than using views and pass through
queries?
Sometimes. I always start with the linked ODBC tables and the same Access
queries I would have had if I were using Access/Jet tables and then I
evaluate them. Those that perform poorly will need to be redesigned.
Whether that redesign involves just changing the Access query or moving to
passthroughs and/or stored procedures will vary from one query to the next.
In general if you have queries that join linked tables then a view on the
server that does the join for you will perform better that doing the join
locally, but that is not a hard rule. Also to consider is that local joins
might still produce an editable result set whereas a joined view will not
unless you use InsteadOf triggers.
You will likely be surprised at just how few of your queries will need to be
converted. Jet/ODBC does a pretty good job of passing the work to the
server even when you use plain old queries against the links. What is
important is getting the server to do the SELECT work. By that I mean if
the server is deciding which rows to send back and Access has to perform
additional processing on those rows then that is not a problem. What you
don't want is for the server to send you ALL the rows so that Access/Jet can
figure out which ones it actually needs.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com