Do most MS Access developers use DRIVER={SQL Server} in the ODBC Connection string? (1 Viewer)

[ions]

Hello MS Access Expert,

If I have

ODBC Driver 17 for SQL Server

in the ODBC connection string I presume that would require me to install this driver on the client's machine? However, suppose the client gets a new computer later on and it has ODBC Driver 18 installed. Will that break my ODBC connection?

Is it sufficient to use DRIVER={SQL Server} in MS Access which comes with every Windows version, although, the below link states that this driver is outdated.
https://stackoverflow.com/questions...s-in-using-driver-sql-server-vs-native-client

Thank you for your input.

 

[pbaldy]

I also use the 17 driver, and yes, it requires the installation on each computer. We install it on new computers as part of a package of things, like the Access runtime. I'll offer this from people way smarter than I:

Detangling the New SQL Server ODBC and OLEDB Drivers

What version of ODBC and OLEDB drivers do you need to use with your SQL Server and Azure SQL backends for your Access applications?

accessexperts.com

 

[ions]

Thanks pbaldy

 

[cheekybuddha]

I posted a function here that enumerates ODBC drivers on a machine - you can use it to see which drivers are available and build your connection string with the most appropriate that is available.

Personally, I would avoid DRIVER={SQL Server}, even though using using ODBC Driver XX for SQL Server is more effort since it requires manual install.

 

[Minty]

We had an occasion recently where a new Azure SQL Database wouldn't accept a connection using the old driver.
Took us ages of fiddling around to work out what was wrong. Eventually switched over to the Version 18 driver and it all worked instantly.

It's worth mentioning that the new drivers are definitely more robust with dropped connections handled in a nicer fashion.

 

[ions]

Thank you for everyone's response

 

[Pat Hartman]

For reasons that are unclear, the only driver automatically installed is the very old SQL Server driver. I use this driver UNLESS I have some reason not to like - the BE database uses data types not supported by the old driver. If you need to use a newer driver, you MUST install it on every PC that will run your app. The easiest way to do this is with the cooperation of your desktop support group. They can include it in their build script so no one forgets to add it.. If your employer doesn't have IT support, then I would avoid using a non-standard driver at all costs because it will always be a nightmare to maintain because someone, probably not you in the future, will always have to remember the driver is non-standard and remember to always install it on new PCs or for new users to the application.

Life would be better if MS could possibly take these silly details into consideration.

 

[ions]

Based on testing, the old SQL Server driver is not performant using Access 2019 and SQL Server 2022. Please see the below thread.

Necessity of using latest Driver

I preferred to use the old SQL Server driver installed on every machine but because of the issues I am forced to use the latest ODBC Driver.

Thank you

 

[ions]

I looked at an older project I worked on back in 2017 and it is using the old SQL Server driver. I recall everything worked fine with the ODBC table links in that project. The old SQL Server driver may still be a viable option if the client has an older version of SQL Server.

 

[cheekybuddha]

Please see the below thread.

Necessity of using latest Driver

Hi,

Thanks for your follow-up.

I'm afraid something may have gone wrong when you posted the above link - it doesn't work.

Are you able to re-post your intended link?

 

[ions]

I'm afraid something may have gone wrong when you posted the above link - it doesn't work.

Thanks for the heads up. The link should work now.