My post on SQL Server Native Client 11.0 for MS Access

http://www.access-programmers.co.uk/forums/showthread.php?t=247756

SQL Linked Tables are always much faster than Access linked tables for me.

Here is my short list to MS Access programmers.

1. Design in MS Access then use the free SQL Server Migration Assistant for Access

This tool looks at a lot of things in the Access table and will get you 92% there.

While I am no stranger to MS SQL Server, why not let automation do the bulk of the work and data type conversions.

2. Download the SQL Server Native Client 11.0 then write code to:

- destroy all linked tables

- recreate DSN-Less linked table objects

I keep one local Access table with the name of the table to link and a check box for if it should be linked.

One subroutine cleans it up and re-establishes the linked tables.

3. Never use Access SQL functions in the queries. The Access query language allows things like the Immediate IF (iif) statement in a query.

For this and all the other Access SQL functions, there is no direct T-SQL translation. That can really slow down a query.

Write an Access generic query with parameters and the TSQL conversion of the Native Lanugage driver will not require any TSQL or even Views.

That includes multiple table joins. There are some great articles that show the fantastic translation of complex multiple table joins vs TSQL.

There is a lot of misinformation about data traffic and such from Access. To be clear, we exclude the IIF and other Access SQL functions from this.

Since Access 2007, table joins with SQL Server Native Client are evaluated and created on the SQL Server side. Have read some of the detailed analysis. For the majority of cases, it is not necessary to create the joins anymore. I use to write something called a SQL PassThrough Query in MS ACCESS. Take the T-SQL string - modify it in code (e.g. change the With statement or parmameter) then pass it to SQL Server and Execute it.

The SQL Server Native Client now appears to make this unnecessary and maybe even less efficient.

4. Get your data from SQL Server, then "Top it off" with Access functions locally. This can be Temp tables or many other methods.

My Access Applications link to multiple databases. They have hundreds of concurrent users. The Access front-end runs on a Citrix Server linked for example to the SQL Server close by. The Citrix client works for PC, Apple and other platforms. The field engineers often have a full rich front end experience with about 16K bandwidth (in rural areas). Basically, use KISS with some decent planning and be very productive.

Granted, my front-end is only 70 MB and the databases are only a few gigabytes. The front-end is very complex regulatory rules.

There is some fantastic advice in the other post. Stick with the basics and learn some of the advanced concepts as needed.

For Access Programming connected to SQL Server back end, visit us at the link above.

This site is the best for all SQL Server quesitons. I truly marval at the level of experts found here.