Access front end with link to SQL Server

[Lightwave]

Access front end with link to SQL Server - Where are the Queries RUN?

I probably should know this but

If you have a series of ETL queries , written and stored in an MS Access front end linked to SQL Server.

If you run a script of those queries - what engine performs the heavy lifting - are the queries passed to SQL server which performs the Query and then passes back the result OR does access pull down the information perform the query and then pass back any updates etc to SQL Server?

Thanks in advance

 

[Frothingslosh]

It depends on if you have the queries set up as pass-through or not.

If they are pass-through queries (they'll show in the Access query list with a globe icon instead of the usual query icon), then SQL Server will do the heavy lifting.

If they are regular queries, the specific execution depends on what the queries are doing. Access will TRY to have SQL server limit the passed recordsets as much as possible, but sometimes - especially when you're using functions that exist in Access but not SQL Server - the back end will have to pass the entire dataset (or even the entire table!) to the front end for Access to do the filtering. The worst situation is probably the use of subqueries - SQL Server tries to run them once and save the results for the remainder of processing, while Access seems to default to running them once per line. (That's a HUGE reason we always recommend 'stacked' queries over subqueries here - it's a ridiculous performance improvement.)

Basically, if you're using large tables/datasets or complex queries, you'll probably want to use a pass-through.

 

[Lightwave]

...... Thanks