Linked MS SQL Server Tables

[Guest]

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?

 

[Michel Walsh]

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

 

[Michel Walsh]

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

 

[Guest]

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.