SQL Driver for MS Access

[pekajo]

Hi,
Still trying to work out connecting ms access front end to sql back end. Seems I need to add an sql connection to my win10 pc. When I go there there are 3 options. (see attached)
Can you tell me which one is best to use or are they used for different applications?
Thanks
Peter

 

[Isaac]

I have usually just picked the one that says "SQL Server". Others may chime in with more advanced expertise

 

[AccessBlaster]

SQL Server Native Client xx.x is the one we normally use.

 

[pekajo]

Thanks for your help.

 

[deletedT]

SQL Server Native Client xx.x is the one we normally use.

From Microsoft if you don't know:

The SQL Server Native Client (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.

About - SQL Server Native Client

Learn about the features of SQL Server Native Client (SNAC). SQL Server Native Client refers to ODBC and OLE DB drivers for SQL Server.

docs.microsoft.com

 

[AccessBlaster]

Thanks for the update Tera.

 

[isladogs]

I remember reading the article in Tera's link some time ago with its reference to the rather strange word 'undeprecated'

Using the latest driver makes sense if you are using the latest features in SQL Server.
However that will require specifically checking and, if necessary, installing it on each PC which has the FE concerned.

However for most purposes either of the two basic SQL Server drivers installed with Access will be sufficient for your needs
...and will be guaranteed to work on all machines running the full version of Access

 

[AccessBlaster]

After reading Tera's post I checked my ODBC config on my home laptop and the Native Client was not available. But at work it is very prevalent, and that was the ticket.

Thanks Colin.

 

[Pat Hartman]

I have usually just picked the one that says "SQL Server". Others may chime in with more advanced expertise

This is the default driver that is loaded with Access. It is very old and is not comparable with newer data types in SQL Server so if you do not have control over the SQL Server table definitions, you might run into a problem if you use this driver. I would use the top one in the list which is a newer version. However, you will have to ensure that the driver you use is available for ALL of your users as well. If you have IT support, they can handle this for you if you ask them to.

 

[isladogs]

Pat is making the same point as I did in post #7 but with the opposite emphasis.
To reiterate, if you are using or intend to use the latest SQL features/datatypes, you need the latest driver.
However if you have no intention of doing so, it is fine to use the very old basic SQL Server driver as it will do the job.