vba 从 Excel 运行 Access 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24437642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running an Access Query from Excel
提问by user3780550
I am attempting to run a query in access from excel, and then have those results pulled into the excel document using ADO in VBA. Unfortunately, I cannot figure out how to run the access query such that data in the active cell of the excel sheet is used as a criteria in the access query.
我正在尝试从 excel 访问中运行查询，然后使用 VBA 中的 ADO 将这些结果拉入 excel 文档中。不幸的是，我无法弄清楚如何运行访问查询，以便将 Excel 工作表活动单元格中的数据用作访问查询中的条件。
I am running Excel and Access 2007. I've included what code I have so far below. Thanks in advance for your help.
我正在运行 Excel 和 Access 2007。我已经在下面包含了到目前为止我所拥有的代码。在此先感谢您的帮助。
Sub testdb()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open "H:\WBC\Lukas\STOP.accdb"
End With
con.Execute "HPRSearch"
'the criteria field is 'Input', and I need to pull it from the active cell on the Excel Sheet
End Sub
回答by VBlades
The first thing you need to do is set up your parametereized query in Access. So, say, Query1 is (where ID is an integer):
您需要做的第一件事是在 Access 中设置参数化查询。所以，比如说，Query1 是（其中 ID 是一个整数）：
SELECT ID FROM Table1 WHERE ID = [MyID];
The brackets around [MyID], if it doesn't resolve to a field name, will be considered a Parameter. Now, say, we want to bring back the record with ID 1. Set up your code in Excel:
[MyID] 周围的括号，如果它不能解析为字段名称，将被视为参数。现在，假设我们要恢复 ID 为 1 的记录。在 Excel 中设置您的代码：
Sub testdb()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
With con
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open "H:\WBC\Lukas\STOP.accdb"
End With
With cmd
.ActiveConnection = con
.CommandText = "Query1"
.CommandType = adCmdStoredProc
.Parameters.Append cmd.CreateParameter("MyID", adInteger, adParamInput)
.Parameters("MyID") = 1
End With
Set rs = New ADODB.Recordset
rs.Open cmd
Do Until rs.EOF
Debug.Print rs.Fields("ID").Value
rs.MoveNext
Loop
rs.Close
con.Close
Set cmd = Nothing
Set rs = Nothing
Set prm = Nothing
Set con = Nothing
End Sub
This reference adInteger found in this line
在此行中找到此参考 adInteger
.Parameters.Append cmd.CreateParameter("MyID", adInteger, adParamInput)
should be replaced with the proper constant that represents the variable type (see here: http://www.w3schools.com/ado/met_comm_createparameter.asp) of the Parameter in your query. In your case, you would set the Parameter value that's represented in this line
应替换为表示查询中参数的变量类型的正确常量（请参见此处：http: //www.w3schools.com/ado/met_comm_createparameter.asp）。在您的情况下，您将设置此行中表示的参数值
.Parameters("MyID") = 1
with the value from your cell.
使用您的单元格中的值。
And that's it. So you create the Connection, create a Command object (which is essentially a reference to your Access query), set the Command object's properties, including the parameter, then have the results brought back in a recordset. Then loop through the recordset and do what you want with the values.
就是这样。因此，您创建了 Connection，创建了一个 Command 对象（它本质上是对您的 Access 查询的引用），设置了 Command 对象的属性（包括参数），然后将结果返回到记录集中。然后循环遍历记录集并对值执行您想要的操作。
