Re: Using SQL Server as the backend

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


.

Relevant Pages

  • Re: [WARNING] Failed to query SPN registration on DC
    ... DNS server: User Specified DNS Server ... UDP port 53 responding to queries: ... iescrkads01.jw-uk.jameswalker.co.uk Unknown ...
    (microsoft.public.windows.server.active_directory)
  • Re: ntp.conf access resctriction
    ... That is allow time service from anyone, but allow only queries ... LAN is not allowed control though. ... your time requests sent to external servers, ... these 'restrict' options have pretty complex/obscure semantics, ...
    (comp.unix.bsd.freebsd.misc)
  • RE: Limit W2K Queries.
    ... please let me know why there is a firewall appliance ... 60Subject: RE: Limit W2K Queries. ... Microsoft CDO for Windows 2000 ... 60>60One stand alone Windows 2000 server (an e-Gap Remote ...
    (microsoft.public.win2000.networking)
  • Re: A few thousands simple queries seem to clog SQL server (for a while)
    ... >queries using the OleDB DataReader, ... the SQL server seems to be clogged and starts failing all ... > dbParams, Object& executeResult) ... > connection to the database. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Alternatives to ADP?
    ... Another thing to look at, which Sylvain touched on, but didn't quite highlight is to write your queries on the SQL Server side, and ... then attach them in Access as either Tables or Pass-through Queries. ... have the disadvantage that you have to worry about linking them in...not so much a problem if your server is reliably in one place, ... So I am now testing the other upsizing option that creates an adp file. ...
    (microsoft.public.access.adp.sqlserver)