通过VBA中的Excel查询从Access执行查询 [英] Execute Query from Access via Excel Query in VBA

问题描述

访问权限保存了一个使用名为"myQuery"的查询构建器设计的查询.数据库通过ODBC连接连接到系统.宏全部启用.

Access has saved a query that was designed with the query builder called 'myQuery'. The database is connected to the system via ODBC connection. Macros are all enabled.

Excel 已建立ADODB连接以通过

Excel Has makes a ADODB connection to connect to the database via

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
 .Provider = "Microsoft.ACE.OLEDB.12.0"
 .Open "MyDatabase.accdb"
End With

通常，您将继续编写SQL，这非常好，然后执行类似的操作

Usually you would go ahead and just write your SQL, which is perfectly fine and then just do something like

Dim sqlQuery As String
sqlQuery = "SELECT * FROM myTable"
Set rs = New ADODB.Recordset
rs.Open sqlQuery, con, ...

但是我想访问保存在Access数据库中的查询.因此，如何在刚刚连接的数据库中调用保存的查询.

But I want to access the query that I saved in the access database. So how do I call the saved query in the database that I just connected.

已经尝试过

1. con.Execute("EXEC myQuery")，但有人告诉我找不到myQuery.
2. rs.打开"myQuery"，con ，但是那个无效并且需要SELECT/etc语句

1. con.Execute("EXEC myQuery") but that one told me it could not be find myQuery.
2. rs.Open "myQuery", con but that one is invalid and wants SELECT/etc statements from it

推荐答案

我认为您可以将其视为存储过程.

I think you can treat it like a stored procedure.

如果我们在Dim sqlQuery As String

 Dim cmd as new ADODB.Command
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "myQuery"
 cmd.ActiveConnection = con

 Set rs = cmd.Execute()

然后在此之后提取记录集的工作.

Then pickup your recordset work after this.

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