从Excel运行访问查询 [英] Running an Access Query from Excel

问题描述

我正在尝试在excel访问中运行查询，然后使用VBA中的ADO将这些结果拉入excel文档。不幸的是，我不知道如何运行访问查询，以便将Excel表单的活动单元格中的数据用作访问查询中的标准。

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和Access 2007.我已经包括了我以下的代码。在此先感谢您的帮助。

I am running Excel and Access 2007. I've included what code I have so far below. Thanks in advance for your help.

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

推荐答案

你需要的第一件事在Access中设置参数化查询。所以说，Query1是（其中ID是整数）：

The first thing you need to do is set up your parametereized query in Access. So, say, Query1 is (where ID is an integer):

SELECT ID FROM Table1 WHERE ID = [MyID];

如果不能解析为字段名称，[MyID]周围的方括号将被视为一个参数。现在说，我们要带回ID 1的记录。在Excel中设置代码：

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:

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

在此行中找到此引用adInteger

This reference adInteger found in this line

.Parameters.Append cmd.CreateParameter("MyID", adInteger, adParamInput)

$ b $应该用代表变量类型的正确常数替换b

（参见这里： http://www.w3schools.com/ado/met_comm_createparameter.asp ）。在您的情况下，您将设置在此行中表示的参数值

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

.Parameters("MyID") = 1

与您单元格的值。

而已。所以你创建了Connection，创建一个Command对象（这实际上是对Access查询的引用），设置Command对象的属性，包括参数，然后将结果带回记录集。然后循环通过记录集，并使用值进行所需的操作。

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.

这篇关于从Excel运行访问查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！