从 Excel vba 中的 SQL Server 数据检索/创建记录集

Question

我想在Excel vba中从SQL Server访问/检索/创建记录集。

我尝试了以下方法，但它们返回错误。

代码 1：

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
sConnString = "Provider=sqloledb; Server=192.168.0.204; Database=REPORTdb2"
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open sConnString 
Set rs = conn.Execute("select * from Table1;")

在发生错误的行conn.Open sConnString：

授权规范无效

代码 2：

sConnString = "Provider=SQLOLEDB;Data Source=192.168.0.204;" & _
              "Initial Catalog=ReportDB2;" & _
              "Integrated Security=SSPI;"
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open sConnString 
Set rs = conn.Execute("SELECT * FROM Table1;")

它抛出一个错误

无法生成 SSPI 上下文

Answer 1

以下代码要求在 VBE 中引用 Microsoft Active Data Objects 2.8 Library 或更高版本：

Public Sub AdoTestConnection()
Dim conServer As ADODB.Connection
Dim rstResult As ADODB.Recordset
Dim strDatabase As String
Dim strServer As String
Dim strSQL As String
Set conServer = New ADODB.Connection
conServer.ConnectionString = "PROVIDER=SQLOLEDB; " _
    & "DATA SOURCE=192.168.0.204; " _
    & "INITIAL CATALOG=REPORTdb2; " _
    & "User ID=sa;" _
    & "Password="
On Error GoTo SQL_ConnectionError
conServer.Open
On Error GoTo 0
Set rstResult = New ADODB.Recordset
strSQL = "set nocount on; "
strSQL = strSQL & "select * from Table1;"
rstResult.ActiveConnection = conServer
On Error GoTo SQL_StatementError
rstResult.Open strSQL
On Error GoTo 0
'To copy the result to a sheet you may use the following code
'It will copy your table 'Table1' to the first sheet in your Excel file.
ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rstResult
Exit Sub
SQL_ConnectionError:
MsgBox "Problems connecting to the server." & Chr(10) & "Aborting..."
Exit Sub
SQL_StatementError:
MsgBox "Connection established. Yet, there is a problem with the SQL syntax." & Chr(10) & "Aborting..."
Exit Sub
End Sub

包含的错误处理

1. 建立与 SQL 服务器的连接，并
2. 尝试将 T-SQL 命令传递给服务器进行处理

您应该能够轻松解决连接到服务器的问题。上面的代码只会在成功时返回 1（用于测试运行）。之后，您可以将 SQL 命令替换为上面示例中的命令。