INTRODUCTION (tldr; question down below)
First off, yes I know DAO:
So answers along those lines aren't helpful to the problem.
I've long since shifted my preferences to NHibernate and other means of connecting to data. However, I'm on a legacy project where the first stage of the plan is to migrate the data from many Access databases to a single SQL Server instance. Easy enough. The data migration has been done and works fine.
However, the application is VB6 code using DAO to connect to Access and I need to change this as minimally as possible for the first stage. The next phase (soon) is to convert the whole application to an ASP.NET MVC site hitting SQL Server properly.
As I search around for how to properly structure the DAO OpenDatabase() function to hit SQL Server, all the answers are the aforementioned "Why do you want to do that?" "You should learn ADODB!" "DAO is for suckers!" nonsense that never addresses the actual question asked. Sadly the original question remains in the search engine even though it's marked "solved" on particular forums, etc...
SO... THE QUESTION
Given all of this, is there ANYONE who still remembers how to connect to SQL Server using DAO OpenDatabase?
Yes, this is comparable to drilling a hole in a piece of wood with a brace and bit? Sure a power drill is better, smarter, faster, and all that goodness... but sometimes the old ways are required.
I had to migrate recently a VB6 with DAO application from SQL7 to SQL 2016, and the process was almost straight forward. I didn't change anything in code, except old SQL Syntax that is obsolete now.
I used ODBC. So, here how I open SQL connection with DAO :
connect = "DSN=<odbcname>;UID=<username>;PWD=<password>;DATABASE=<database>"
Set datab = rdoEnvironments(0).OpenConnection("", rdDriverCompleteRequired, False, connect)
This is the solution for us, for having less changed to do in our old app. Note this is not the only way to do this.
Remember you have to use 32 bits ODBC driver. If you are on 64 bits system, take the good one (C:\Windows\SysWOW64\odbcad32.exe). And I think I have used SQL Server driver. SQL Native client are too recent for DAO (I think). You may have to try many.