在 Excel VBA 中从 Access 2007 运行参数查询时出错
我正在尝试从 Excel 2007 VBA 脚本在 Access 2007 数据库中运行查询。 Access 查询具有名为“年”和“月”的参数。
我正在尝试让以下代码正常工作：
Sub RunMyQuery()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim dbPath As String
Dim stQRY As String
Dim stCon As String
Dim cmd As New ADODB.Command
Dim prmYear As New ADODB.Parameter
Dim prmMonth As New ADODB.Parameter
dbPath = "<PATH_TO_MY_DB>"
stCon = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & dbPath & ";"
cn.Open (stCon)
cn.CursorLocation = adUseClient
Set cmd.ActiveConnection = cn
Set prmYear = cmd.CreateParameter("Year", adNumeric, adParamInput, , 2011)
Set prmMonth = cmd.CreateParameter("Month", adNumeric, adParamInput, , 5)
cmd.Parameters.Append prmYear
cmd.Parameters.Append prmMonth
cmd.CommandText = "SELECT * FROM [Month_Totals]"
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
Sheets("Sheet1").Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
当我运行此代码时，代码在“cmd.Execute”上停止
运行时错误“-214217900 (80040e14)”：
FROM 子句中存在语法错误。
我错了什么？
命令文本对我来说似乎很简单。我是不是错过了什么？
我是否滥用了 ADODB.Command 的参数功能？我不认为这是这里的问题，因为我尝试使用非参数化查询代替 Month_Totals 来运行相同的脚本，并得到相同的错误。
I'm trying to run a query in an Access 2007 database from an Excel 2007 VBA script. The Access query has parameters called "Year" and "Month".
I'm trying to get the following code to work:
Sub RunMyQuery()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim dbPath As String
Dim stQRY As String
Dim stCon As String
Dim cmd As New ADODB.Command
Dim prmYear As New ADODB.Parameter
Dim prmMonth As New ADODB.Parameter
dbPath = "<PATH_TO_MY_DB>"
stCon = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & dbPath & ";"
cn.Open (stCon)
cn.CursorLocation = adUseClient
Set cmd.ActiveConnection = cn
Set prmYear = cmd.CreateParameter("Year", adNumeric, adParamInput, , 2011)
Set prmMonth = cmd.CreateParameter("Month", adNumeric, adParamInput, , 5)
cmd.Parameters.Append prmYear
cmd.Parameters.Append prmMonth
cmd.CommandText = "SELECT * FROM [Month_Totals]"
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
Sheets("Sheet1").Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
When I run this, the code stops on "cmd.Execute" with
Run-time error '-214217900 (80040e14)':
Syntax error in FROM clause.
What am I getting wrong?
The command text seems simple enough to me. Am I missing something there?
Am I misusing the parameters functionality of ADODB.Command? I don't think that's the problem here, because I've tried running this same script with a non-parametrized query substituted for Month_Totals, and gotten the same error.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我相信这些参数仅在您在访问中使用保存的查询时使用。我将通过将参数移动到 SQL 语句中来解决您的问题。
改变
到
I believe The parameters are only for use when you are using a saved query in access. I would solve your problem by moving the parameters into the SQL statment.
Change
to
返回错误的原因是命令类型设置为adCmdtable，但该命令没有引用表，而是引用了SQL字符串，因此：
接下来，为了返回特定数据，您需要包含一个 WHERE 子句，其中字段名称的参数顺序正确：
The reason that this is returning an error is that command type is set to
adCmdtable, but the command does not reference a table, it references an SQL string, so:Next, in order to return specific data, you need to include a WHERE clause, with field names in the correct order for the parameters: