从Excel运行访问查询并将参数传递给查询 [英] Run access query from Excel and pass parameters to the query

问题描述

如何从Excel VBA代码或宏在MS Access db中执行查询.MS-Access查询接受一些参数，这些参数需要从Excel传递.谢谢

解决方案

这里是一种可能:

  Dim cn作为对象昏暗的strFile作为字符串Dim strCon作为字符串昏暗的strSQL作为字符串strFile ="C:\ docs \ Test.mdb"strCon ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source ="&strFile''后期绑定，因此不需要参考设置cn = CreateObject("ADODB.Connection")cn.打开strConstrSQL =将表插入ATable(AField)" _&"VALUES(& Sheet1.[A1]&")cn.执行strSQLcn.关闭设置cn =否 

您还可以在sql中内联引用Excel中的数据集.

编辑评论

使用命令:

  strSQL =选择*来自表" _&"WHERE AField = @AField"与cmd设置.ActiveConnection = cn.CommandText = strSQL.CommandType = 1'adCmdText''ADO数据类型通常非常特殊''adSmallInt = 2;adParamInput = 1.Parameters.Append .CreateParameter("@ AField"，2，1 ,, Sheet1.[A1])结束于设置rs = cmd.Execute 

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

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

解决方案

Here is one possibility:

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

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

EDIT re comments

Using a command:

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 

See also: http://support.microsoft.com/kb/181782

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