DSN Less Connection (MS Access to SQL2016)

Question

I have this solution that works when I want to convert linked tables to a DSNLess Connection:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

But it's always been an Access DB (2010 or 2013) to SQL2012. I now have a SQL2016 instance that I'm trying to make a DSNLess Connection to. So here is what I've tried:

Running the code as given in the article.. This gives me an SSL Security Error.

Changing the Connection to use:

strConnectionString = "Provider=SQLNCLI11;" & _
   "Server=" & ServerName & ";" & _
   "Database=" & DatabaseName & ";" & _
   "Trusted_Connection=Yes;"

When I do this, it gives me "Could not find installable ISAM". What do I need to change to get this to work with SQL2016?

Answer 1

Well, right after posting this I found my solution. In this article I found you can just list the driver:

https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard

So, what I did was make my connection be this:

strConnectionString = "ODBC;Driver={SQL Server Native Client 11.0};" & _
   "Database=" & DatabaseName & ";" & _
   "Server=" & ServerName & ";" & _
   "Trusted_Connection=Yes;"

And it worked perfectly.