sql-server - 可以使用 ADODB 将 Excel 连接到 Azure SQL Server DW 吗？

Question

我正在尝试从 Excel 到较新版本的 SQL Server 运行一个简单的查询。我过去曾多次这样做，但总是针对标准 SQL Server 数据库。现在，我正在使用一种新动物，即 Azure SQL Server 数据仓库。我正在使用非常通用的 VBA 代码连接到 DW。

Sub TryMe()

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

pswd = "my_pwd"
user = "my_used_ID"
dbName = "my_DB_name"
server = "server_name.database.Windows.net"

'Setup the connection string for accessing MS SQL database
   'Make sure to change:
       '1: PASSWORD
       '2: USERNAME
       '3: REMOTE_IP_ADDRESS
       '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB;Password=pswd;User ID=user;Data Source=server;Use Encryption for Data=False;Initial Catalog=dbname"

    'Opens connection to the database
    cnn.Open ConnectionString
    'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

    'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT TOP 10 * FROM myBigTable"

    'Performs the actual query
    rst.Open StrQuery, cnn
    'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

这应该是一件非常简单的事情，但我不断收到一条错误消息:“SQL Server 不存在或访问被拒绝”。

错误发生在这一行:

cnn.Open ConnectionString

我设置了对 Microsoft ActiveX Data Objects 2.0 Library 的引用.

有没有人试过这个并真正让它工作？

Answer 1

您没有将变量粘贴到连接字符串中。和老SQLOLEDB提供程序太旧，无法连接到 Azure。您需要从 here 安装新的.

ConnectionString = "Provider=MSOLEDBSQL;Password=" & pswd & ";User ID=" & user & ";Data Source=" & server & ";Use Encryption for Data=False;Initial Catalog=" & dbName & ""

如果不想安装 MSOLEDBSQL，可以使用 newer ODBC drivers 之一.连接字符串有点奇怪，因为您使用 OleDb Provider for ODBC Drivers .例如:

ConnectionString = "Provider=MSDASQL;Extended Properties=""Driver={ODBC Driver 17 for SQL Server};pwd=" & pswd & ";uid=" & user & ";Server=" & server & ";database=" & dbName & """"