使用VBA在Excel中查询SQL Server表

Question

我正在尝试使用VBA查询Microsoft Excel中的表格。我编写了一些代码来完成此任务，但是我一直收到错误消息：运行时错误'1004'：称它是一个常规ODBC错误。

我不确定需要做什么才能使这段代码正常运行，以便我可以查询此表格。

我正在使用SQL Server Express，我连接到的服务器是：.\SQLEXPRESS 数据库： Databaselink 查询产品表 VBA代码：

Sub ParameterQueryExample()
'---creates a ListObject-QueryTable on Sheet1 that uses the value in 
'        Cell Z1 as the ProductID Parameter for an SQL Query
'        Once created, the query will refresh upon changes to Z1. 

Dim sSQL As String
Dim qt As QueryTable
Dim rDest As Range

'--build connection string-must use ODBC to allow parameters
Const sConnect = "ODBC;" & _
    "Driver={SQL Server Native Client 10.0};" & _
    "Server=.\SQLEXPRESS;" & _
    "Database=TSQL2012;" & _
    "Trusted_Connection=yes"

'--build SQL statement
sSQL = "SELECT *" & _
        " FROM TSQL2012.Production.Products Products" & _
        " WHERE Products.productid = ?;"

'--create ListObject and get QueryTable
Set rDest = Sheets("Sheet1").Range("A1")
rDest.CurrentRegion.Clear  'optional- delete existing table

Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _
    Source:=Array(sConnect), Destination:=rDest).QueryTable

With qt.Parameters.Add("ProductID", xlParamTypeVarChar)
    .SetParam xlRange, Sheets("Sheet1").Range("Z1")
    .RefreshOnChange = True
End With

'--populate QueryTable
With qt
    .CommandText = sSQL
    .CommandType = xlCmdSql
    .AdjustColumnWidth = True  'add any other table properties here
    .BackgroundQuery = False
    .Refresh
End With

Set qt = Nothing
Set rDest = Nothing
End Sub

- Mitchell Walker

---

看这里：http://www.connectionstrings.com/sql-server/。连接到 SQL Server 有几种方式和提供程序...尝试不同的连接设置。 - Maciej Los

这发生在哪一行？给我们一个提示。 - Nick.McDermaid

Answer 1

解决这个问题的方法如下：

Dim lo As ListObject
Set lo = ActiveSheet.ListObjects.Add(xlSrcExternal, _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SERVER_NAME;Data Source=CONNECTION_ADRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False", _
True, xlYes, Range("A2"))

lo.QueryTable.CommandType = xlCmdSql
lo.QueryTable.CommandText = "SELECT * FROM TABLE_NAME"

With lo.QueryTable.Parameters.Add("Currency code", xlParamTypeVarChar)
    .SetParam xlRange, ActiveSheet.Range("A1")
    .RefreshOnChange = True
End With

lo.QueryTable.Refresh BackgroundQuery:=False

您可以从 SQL 查询中创建一个 listObject。

- Hector C