Passthrough vs Local Know your Queries!

I am transferring a database to SQL for both performance and scale-ability issues.

We are retaining the Access User Front-Ends due to the familiarity that the User has.

The Front ends make use of (local) Queries based on the underlying (SQL) data.

I am trying to establish what is the best method of replicating these queries?

Better to keep the Front end local queries or somehow call a view in the SQL Server? or would a formulated pass-through query bee better.

Your thoughts are much appreciated

This thread has some very similar questions and answers - note the bits about UDF's and VBA specific functions.
https://www.access-programmers.co.uk/forums/showthread.php?t=301830

it depends - well designed tables/relationships combined with well written queries should be as fast in SQL Server as in Access, subject to network performance issues. So other than any changes to table/field names you would have your linked tables linked to sql server tables rather than access tables and queries would therefore remain the same. Depending on your version of access you can use the upsizing wizard.

Things like SQL Server using % while Access uses * for Like criteria will be converted automatically by the ODBC driver

SQL Server does have a wider language range so there may be things that will run better rewritten as a passthrough query. SQL Server does not support Crosstab queries, so these would need to be rewritten

performance is usually the last reason for moving to sql server, it can be slower due to the server being shared across many apps for example. See this link (post #1) about things to consider - if you haven't addressed them before moving to sql server, you will almost certainly need to address them as part of the move.

https://www.access-programmers.co.uk/forums/showthread.php?t=291269&highlight=indexes

I am much obliged to you all for your comments and thoughts. More Homework me thinks !
Thanks Guys