I have a VBA script that calls SQL Server. This script first tests for a valid connection to the SQL server, and, if valid, it then runs a Power Query.
My "test connection" scriptlet is something like:
Function TestSQLConnection()
Dim cnn As ADODB.Connection
TestSQLConn = False
Set cnn = New ADODB.Connection
cnn.Open "Provider=sqloledb;Data Source=myserver;Initial Catalog=MyDB;Integrated Security=SSPI;"
If cnn.State = adStateOpen Then
TestSQLConnection = True
cnn.Close
End If
End Function
My "Power Query" scriptlet is something like:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""SomeTable"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [SomeTable]")
.ListObject.DisplayName = "SomeTable"
.Refresh BackgroundQuery:=False
End With
My VBA script was formerly pointing to SQL 2014, which has both the old sqloledb driver and the new msoledbsql driver. But now, my script is pointing to SQL 2017, which didn't have the msoledbsql driver. On this server, the "test connection" scriptlet was failing. As such, I installed msoledbsql on the server, and I modified my "test" connection scriptlet to:
cnn.Open "Provider=msoledbsql;Data Source=myserver;Initial Catalog=MyDB;Integrated Security=SSPI;"
Everything seems to work now. However, I don't understand why I didn't need to modify anything in the Power Query scriptlet. Does the OLE DB call in Power Query just "know" to use the appropriate driver?
The OLEDB call is not what Power Query is using to connect to your database, rather it's what Excel is using to retrieve the end results from what Power Query is doing. You can see that in the "Microsoft.Mashup.OleDb" source, which is basically Power Query's backend. The connection to your database is happening using Power Queries own SQL Server connector, which presumably works fine between different versions.