使用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
数据库:
查询产品表
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:
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屋!