ODBC the best

Question

I am using MS access 2016 connected to sql server 2016.

I have got a question:

Which is the best odbc to use?

* SQL Server Native Client 11.0 ODBC Driver
* SQL Server Native Client 10.0 ODBC Driver
* SQL Native Client 9.0 ODBC Driver
* Microsoft SQL Server ODBC Driver
* ODBC Driver 17 for SQL Server

Thanks

Marco Dell'Oca

Answer 1

My rule is to use the most recent version that is widely supported, and ideally already installed by your users.

---

-Tom. Microsoft Access MVP

Answer 2

Native Client 11 is the one I default to. It's always been the best performer for me.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 3

The latest:

Microsoft® ODBC Driver 17 for SQL Server®

And it is not installed by default on users' workstations.

---

Gustav Brock

Answer 4

do you know if there are any differences between then?

Thanks

Marco Dell'Oca

Answer 5

Yes, the old "native driver" - installed by default in Windows - does not support the newer data types like Datetime2.

The latest drivers are mandatory when Azure SQL is chosen as backend, and - at least in some cases - they are faster.

If you plan to use only the good old datatypes like Datetime with a Windows SQL Server, you may often run perfectly well with the native driver.

---

Gustav Brock

Answer 6

Hi Gustav,
so if I choose SQL Azure as backend I have to use the last one:
Microsoft® ODBC Driver 17 for SQL Server®

Thanks to everybody

Marco Dell'Oca

Answer 7

Well, as suggested, the later drivers in most cases are the best choice. And for Azure, you have to use the native drivers.

The only case one can make for the standard “SQL server ODBC driver” is it is already installed by default on windows.

So, due to a large deployment I was involved with, we went with the “legacy” and older SQL server ODBC driver. We did this because we did not want to have to ensure that each workstation had a later driver. So the native drivers are not installed by default, and as a result, you need an installer, or some means to check + ensure that the native driver is installed (they are not installed by default).

The other common issue with using the legacy “SQL driver” is they do not support datetime2 data types. And the SSMA upsizing wizard and tools by default use datetime2. If you link to sql server using the legacy driver, then it sees datetime2 sql columns as “text”. Needless to say, having the ODBC driver see date columns as text creates a real big mess.

So, if you do go with the legacy “older” SQL drivers, just be aware that you can’t use the newer datetime2 columns.

Only each person can “decide” or “determine” if the efforts of installing the newer native drivers is more or less hassle then say avoiding use of the datetime2 data types.

As noted, I have a number of installs were I went with the legacy ODBC drivers because of such large numbers of workstations, and also they were rather “locked” down in terms of our ability to install additional software such as the newer ODBC drivers.

If you have any choice in this matter, or say are using a custom installer for your application, then certainly use the newer native drivers.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 8

Albert thanks for your thorough explanation.
In my MS Access application I use the ODBC drivers pre-installed on windows and I have never had any problems.
Now I'm transferring my application to SQL Azure and then I'll have to use the latest ODBC drivers released by Microsoft.
Also because I'm checking that they work very well.

Thank you
Marco Dell'Oca