Re: Linked MS SQL Server Tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you for your help. I tested a query on my system first using jet and
then a SQL Stored Procedure. There was no comparison is the reply time - the
Stored Procedure easily won out.

"Michel Walsh" wrote:

There was an extra dot, to be removed:

SELECT authors.city
FROM authors LEFT JOIN titles
ON ( authors.au_id = titles.au_id
AND authors.city = 'Iqaluit' )


Vanderghast, Access MVP


"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:uFBuasnEIHA.1212@xxxxxxxxxxxxxxxxxxxxxxx
Jet is the master of the transaction, in that case. MS SQL Server is
limited to supply/maintain its data, as any other heterogeneous source.
Remember that your query can refer to a table stored natively in Jet,
another table in MS SQL Server and a third table, in the said query, can
come from Excel !


You can run the following query (with all tables linked to Pubs, in MS SQL
Server)

SELECT authors.city
FROM authors LEFT JOIN titles ON ( authors..au_id = titles.au_id AND
authors.city = 'Iqaluit' )


and see that the result is like JET wants it (NO ROW in the result) while,
if it was MS SQL Server that was in charge, all the rows from authors will
be in the result. That proves that Jet is in charge of the execution plan.


Vanderghast, Access MVP


"JonG" <JonG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:45E57AED-2D95-40F6-AFC6-92779B69BBE1@xxxxxxxxxxxxxxxx
Using Linked MS SQL Server Tables in an mdb Access Database, if a query
(not
passthrough) includes joins, where, groupby (summations), and order by
statements, what part of the query is executed on the SQL Server and what
part is executed locally in Access?





.

Relevant Pages

  • Re: GROUP BY vs DISTINCT
    ... In Jet, the second query uses the same plan than MS SQL ... MS SQL Server uses 1 UET! ...
    (microsoft.public.access.queries)
  • Re: Stored Procedures v Views
    ... Build a stored procedure, ... The goal here will be to not use any dynamic sql like you used in Access. ... If the query is very complex, but frankly, if you have to do this, you are ... > they make are then used to create the SQL statement which is then used to ...
    (microsoft.public.sqlserver.programming)
  • Re: upsizing to sql 2005
    ... A query in Access-Jet will be started and under the supervision of Jet, ... Jet syntax parsing and evaluation. ... so, in this case, into MS SQL Server. ...
    (microsoft.public.access.queries)
  • RE: Class Module Choices
    ... SQL Server has Stored Procedures. ... Perhaps you meant Stored Query with is different. ... When I said stored procedure, ... I don't know how to assign a value to Param1 within the execute command. ...
    (microsoft.public.access.formscoding)
  • Re: Stored Procedure
    ... We cannot delare variables within the SQL statements we build in Access. ... If I want 14284 I should do a seperate query whereas in a stored procedure I ... This is preferable to having the client submitting all those queries ...
    (microsoft.public.access.modulesdaovba)