Use Microsoft SQL Server Native Client 11.0 for the Best Performance
[Update] There is a newer version of this post here.
In our experience in using linked tables with Access, we get the best response using Native Client 11.0 with DSN-less tables. You could use ODBC 11 for SQL Server but then you would NOT be able to use ADODB, so we prefer the latter and not the former.
Microsoft does a great job of hiding Native Client 11.0 on their site, so I’ve done the hard work, you can download the files right from this blog. If you are running 64-bit Windows (even though you might be running 32-bit Access), download the 64-bit version. If you are still using 32-bit Windows, download the 32-bit version.
Please join me on September 8 for the opening season of the SQL Server with Access group!
Next month’s presentation will be on “When to use a Stored Procedure, Pass Through Query, View or ADODB in Access?”, it will be at 6:30 PM CST and for details please click here. While you’re at it, why not register for the group at AccessUserGroups.org? looking forward to seeing you that evening!
Great links to download the dll libraries !!
And great advice …
Do you have any data to back this up? As far as I’ve always understood the new SQLNCLI provide new features (I guess speed improvements could be a feature)
the native client works well with linkedtables and Azure. The only problem was to find it. If you search make sure you do not download ODBC client. This one will not work with Azure.
just wanted to ask if you have any examples on using the transaction management with this native client.??
Great article with good advice. Just wanted to encourage readers to try it out. It has a great return on the time invested
I have been using this method for 5 years with the various Native Client versions and DSN-Less connections. Was thrilled to see the Microsoft demo for Azure with Access used the same Native Client concept.
In my case, Native Client is used on a Citrix environment to distribute the rich fullness interface Access offers with VBA over very small bandwidth. The free Native Client option reduces much of the pain for maintaining ODBC versions on each client workstation.