http://www.utteraccess.com/forum/Beginning-Sql-Server-Deve-t1732935.html

It is lengthy, but it covers the subject well. This should cover 95% of how to make it work the most efficient way.

Access 2007 has some great advantages to translating Access SQL to T-SQL to run in SQL Server.

Access 2010 with SQL Server back end is very powerful.

My method is to use code to: wipe clean all the linked tables.

Create linked tables in Access using the SQL Server Native Client. It is a free MS download. Avoids using the ODBC dialogue box.

When many tables are joined and used often - Create a view on SQL Server and use it as an attached table.

When writing a query in MS Access, there are do's and don'ts.

Do include a Where clause. The SQL Native Client will convert it to native T-SQL and only return the record(s).

This is true for Form filters and actual Access SQL.

Avoid the Like and other general statements.

Never use the MS Access SQL functions. (e.g. IIF([Name] > [Register], "New", "Old")) There is no equal in T-SQL.

Instead, run a nice SQL statement, return the recordset, then run a 2nd MSAccess query against the resulting recordset to use the custom funcitons and formatting we all love in Access. This is called "Topping Off the Query".

Rapid Prototype Cloud Based Applications with MS Access.

Running MS Access on a Citrix Server nearby to the SQL Server is extremely fast. Adding a new MS Access 2010 (or 2013) application can be very efficient. With Citrix, MS Access runs on PC, Mac and other platforms with very little bandwidth.

An application can be prototyped and securely distributed to get data entry and valuable customer feedback.

The rich user environment, class code modules, and countless events can lead to complex rule-based applicaitons reducing the need for the end-user to refresh the form.

If the user community is under 250 concurrent users, an Access front-end with a SQL Server back end can be a very fast and efficient soltuion. When combined with Citrix, it can be deployed very quickly.