使用VBA使用参数化查询在Excel中查询SQL Server [英] Querying a SQL Server in Excel with a parameterized query using VBA

问题描述

我正在使用VBA查询Microsoft Excel中的表。我已经写了一些代码来尝试完成这个任务，但是我不断得到一个运行时错误'1004'，表示它是一般的ODBC错误。我不知道我需要做什么才能使此代码正常运行，以便我可以查询此表。

我正在使用SQL Server Express，我正在连接到的服务器： .\SQLEXPRESS

数据库：

Databaselink

查询产品表
VBA代码：

  Sub ParameterQueryExample（）
'---在Sheet1上创建一个ListObject-QueryTable，它使用
'Cell Z1中的值作为ProductID参数SQL查询
'一旦创建，查询将刷新Z1更改。 
 
 Dim sSQL As String 
 Dim qt As QueryTable 
 Dim rDest As Range 
 
 
'--build连接字符串 - 必须使用ODBC允许参数
 Const sConnect =ODBC; &安培; _ 
Driver = {SQL Server Native Client 10.0}; &安培; _ 
Server = .\SQLEXPRESS; &安培; _ 
Database = TSQL2012; &安培; _ 
Trusted_Connection = yes
 
 
'--build SQL语句
 sSQL =SELECT *& _ 
FROM TSQL2012.Production.Products Products& _ 
WHERE Products.productid =？; 
 
 
'--create ListObject并获取QueryTable 
设置rDest = Sheets（Sheet1）。Range（A1）
 rDest.CurrentRegion.Clear'可选 - 删除现有表
 
 
设置qt = rDest.Parent.ListObjects.Add（SourceType：= xlSrcExternal，_ 
 Source：= Array（sConnect），Destination：= rDest ）.QueryTable 
 
 
'--add参数到QueryTable  - 使用单元格Z1作为参数
使用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在此添加任何其他表属性
 .BackgroundQuery = False 
 .Refresh 
结束
 
 
设置qt = Nothing 
设置rDest = Nothing 
 End Sub 
  

解决方案

我发现这个Stack Overflow问题Google搜索。看起来好像没有人尝试过回答，所以这是我最后做的。不要使用QueryTable，而是使用ADO命令对象，如此MSDN文章所示。 p>

MSDN示例：

  Dim Conn1 As ADODB.Connection 
 Dim Cmd1 As ADODB.Command 
 Dim Param1 As ADODB.Parameter 
 Dim Rs1 As ADODB.Recordset 
 
 Dim i As Integer 
 
'Trap any error /例外。 
错误恢复下一步
 
'创建和打开连接对象。 
 Set Conn1 = New ADODB.Connection 
 Conn1.ConnectionString =DSN = Biblio; UID = admin; PWD =; 
 Conn1.Open 
 
'创建命令对象。 
 Set Cmd1 = New ADODB.Command 
 Cmd1.ActiveConnection = Conn1 
 Cmd1.CommandText =SELECT * FROM Authors WHERE AU_ID<？ 
 
'创建参数对象。 
设置Param1 = Cmd1.CreateParameter（，adInteger，adParamInput，5）
 Param1.Value = 5 
 Cmd1.Parameters.Append Param1 
设置Param1 = Nothing 
 
'打开记录集对象。 
设置Rs1 = Cmd1.Execute（）
  

I'm trying to query a table in Microsoft Excel using VBA. I've written up some code to try and accomplish this task, but I keep getting a run-time error '1004' saying it's a General ODBC error. I'm not sure what I need to do to get this code to run properly so I can query this table.

I'm using SQL Server Express, the server I'm connecting to: .\SQLEXPRESS

Database:

Databaselink

Querying the products table VBA Code:

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


'--add Parameter to QueryTable-use Cell Z1 as parameter
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

解决方案

I found this Stack Overflow question with a Google search. It does not look like anyone has tried answering it, so here's what I ended up doing. Instead of using "QueryTable", use an ADO command object as done in this MSDN article.

MSDN Example:

Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Param1 As ADODB.Parameter
Dim Rs1 As ADODB.Recordset

Dim i As Integer

' Trap any error/exception.
On Error Resume Next

' Create and Open Connection Object.
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = "DSN=Biblio;UID=admin;PWD=;"
Conn1.Open

' Create Command Object.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?"

' Create Parameter Object.
Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5)
Param1.Value = 5
Cmd1.Parameters.Append Param1
Set Param1 = Nothing

' Open Recordset Object.
Set Rs1 = Cmd1.Execute()

这篇关于使用VBA使用参数化查询在Excel中查询SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！