SQL Server connection string variances - Error 3706 Provider not found

[JP Romano]

Hey all...

I have a spreadsheet with some VBA code that connects to a SQL table (ADOB connection to SQL Server 2014) - works beautifully 90% of the time for the bulk of my users.

However, I have multiple users in multiple locations, with varying versions of MS Office (2010, 2013, 2016... some 32 bit, some 64). Some users (about 5 out of 90 so far) get the 3706 error message "provider cannot be found" which I assume relates to the ODBC drivers installed, but I'm not really sure.

I've varied the connection strings with some success, but with so many versions out there, it's hard to know who has what and make the appropriate modifications on the vba side

Here are the connection strings that I've tried (all of them work for me):
>>> What happened to the links to wrap the code ???<<<

cn.Open "Provider= SQLOLEDB; Data Source=OBDEVSQLAGL05; Initial Catalog=DCollection;" & _
"User Id=<<>>; Password=<<>>;"

cn.Open "Provider= SQLNCLI10; Data Source=OBDEVSQLAGL05; Initial Catalog=DCollection;" & _
"User Id=<<>>; Password=<<>>;"

cn.Open "Provider= SQLNCLI11; Data Source=OBDEVSQLAGL05; Initial Catalog=DCollection;" & _
"User Id=<<>>; Password=<<>>;"

I'm not even sure which drivers would be needed... I have a bunch and have asked a few of those getting the error to get them installed, too:
ODBC Driver 11 for SQL Server
SQL Server
SQL Server Native Client 10.0
SQL Server Native Client 11.0

Do any of you know if there is a better way to do this? I don't want to have to have these users open tickets to our IT department to install drivers if it's not necessary (we don't have admin rights to our desktops).

Thank you!

[usommer]

Thanks for this. I had the same problem, and through sheer dumb luck the first connection string in the list above did the trick.