Connecting to SQL server

[CedarTree]

Hi - is there a mostly trouble free way to make sure front-end connect well to SQL server? We share an Access front-end with multiple users and sometimes they have SQL Server driver, and sometimes ODBB Server 17, so getting the right connection string can be tricky. In the past, I've used code to test which SQL driver the end user has on their laptop. Is there an easier way you all use? THANKS!!!

 

[theDBguy]

Maybe you could try using the Native Client with the idea that all users would have it installed automatically. Just a thought...

 

[CedarTree]

Maybe you could try using the Native Client with the idea that all users would have it installed automatically. Just a thought...

Thanks - unfortunately I've found not all do.

 

[Minty]

This will depend on your environment but, you should check which driver they have installed via VBA and then, get them to all install the one you need to use.
We use ODBC 17 , and are migrating towards using version 18.

If you are in a corporate system then your IT can install either through group policy on domain login.
If not make it a prerequisite for installing and running the front end. Having mixed drivers can lead to performance issues and things not working as expected.

 

[Pat Hartman]

Everyone has the same basic SQL Server driver installed. The downside is it is circa 2003 and so any newer SQL Server data type is not supported. The biggest problem is DateTime.

 

[sonic8]

Everyone has the same basic SQL Server driver installed. The downside is it is circa 2003 and so any newer SQL Server data type is not supported. The biggest problem is DateTime.

Totally true.
However, it should be noted that many databases do not strictly need the new DateTime2 or Date data types and they are only in there because some "helpful" wizard suggested to use them. They often can be converted to the older DateTime data type, which is fully supported by the old "SQL Server" ODBC driver.

 

[Minty]

DateTime2 is a right royal PITA when used with Access most of the time.
I've stopped using it completely due to various issues that aren't solved even using the version 17 ODBC driver, and the latest versions of Access.

 

[Pat Hartman]

Unless I have to use one of the newer SQL Server data types, I stick with what the SQL Server driver can handle. Distributing newer ODBC drivers is a PITA unless you have a good IT support team that can automate it for you.