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
1 个解决方案
#1
3
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]没有解析为字段名称,则[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对象的属性(包括参数),然后将结果带回记录集。然后遍历记录集并使用值执行所需操作。
#1
3
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]没有解析为字段名称,则[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对象的属性(包括参数),然后将结果带回记录集。然后遍历记录集并使用值执行所需操作。