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

ions

Access User
Local time
Yesterday, 19:50
Joined
May 23, 2004
Messages
792
Hello MS Access Expert,

If I have
Code:
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

Wino Moderator
Staff member
Local time
Yesterday, 19:50
Joined
Aug 30, 2003
Messages
36,137
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:

 

ions

Access User
Local time
Yesterday, 19:50
Joined
May 23, 2004
Messages
792
Thanks pbaldy
 

cheekybuddha

AWF VIP
Local time
Today, 03:50
Joined
Jul 21, 2014
Messages
2,337
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

AWF VIP
Local time
Today, 03:50
Joined
Jul 26, 2013
Messages
10,380
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

Access User
Local time
Yesterday, 19:50
Joined
May 23, 2004
Messages
792
Thank you for everyone's response
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:50
Joined
Feb 19, 2002
Messages
43,587
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

Access User
Local time
Yesterday, 19:50
Joined
May 23, 2004
Messages
792
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
 
Last edited:

ions

Access User
Local time
Yesterday, 19:50
Joined
May 23, 2004
Messages
792
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.
 

Users who are viewing this thread

Top Bottom