如何从Excel VBA code或宏执行在MS Access数据库的查询。 MS-Access查询接受一些参数，需要被从Excel通过。 谢谢

How to execute a query in ms access db from Excel VBA code or macro. MS-Access query accepts some parameters, that needs to be passed from Excel. Thanks

推荐答案

下面是一种可能：

Dim cn As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

strFile = "C:\docs\Test.mdb"

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile

''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")

cn.Open strCon

strSQL = "INSERT INTO ATable (AField) " _
& "VALUES (" & Sheet1.[A1] & ")"

cn.Execute strSQL

cn.Close
Set cn = Nothing

您也可以参考在线的SQL从Excel中的数据集。

You can also refer in-line in the sql to a dataset from Excel.

编辑回复意见

使用命令：

strSQL = "SELECT * FROM ATable " _
& "WHERE AField = @AField"

With cmd
    Set .ActiveConnection = cn
    .CommandText = strSQL
    .CommandType = 1 'adCmdText

    ''ADO Datatypes are often very particular
    ''adSmallInt = 2 ; adParamInput = 1
    .Parameters.Append .CreateParameter("@AField", 2, 1, , Sheet1.[A1])

End With
Set rs = cmd.Execute 

另请参阅： http://support.microsoft.com/kb/181782