从Excel中运行Access查询，并通过paramerts到查询 [英] Run access query from excel and pass paramerts to the query

问题描述

如何从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

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