通过 VBA 中的 Excel 查询从 Access 执行查询

Execute Query from Access via Excel Query in VBA

提问by toxicate20

Accesshas 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.

Access保存了一个查询，该查询是使用名为“myQuery”的查询构建器设计的。数据库通过 ODBC 连接连接到系统。宏全部启用。

ExcelHas makes a ADODB connection to connect to the database via

Excel已建立 ADODB 连接以通过以下方式连接到数据库

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

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

通常你会继续写你的 SQL，这完全没问题，然后做一些类似的事情

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

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.

但是我想访问我保存在访问数据库中的查询。那么我如何在我刚刚连接的数据库中调用保存的查询。

Tried already

已经试过了

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

1. con.Execute("EXEC myQuery")但那个告诉我它无法找到 myQuery。
2. rs.Open "myQuery", con但那个是无效的，并希望从中获得 SELECT/etc 语句

回答by Bmo

I think you can treat it like a stored procedure.

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

If we start right before Dim sqlQuery As String

如果我们早点开始 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.

然后在此之后拿起您的记录集工作。

回答by Fionnuala

You were nearly there:

你快到了：

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
 .Provider = "Microsoft.ACE.OLEDB.12.0"
 .Open "z:\docs\MyDatabase.accdb"
End With
con.Execute "MyQuery"

Just leave out Exec.

只留下 Exec。

You can add parameters, too, this is a little old, but should help: update 2 fields in Access database with Excel data and probably a Macro

您也可以添加参数，这有点旧，但应该会有所帮助：使用 Excel 数据更新 Access 数据库中的 2 个字段，可能还有一个宏

回答by Steve

I was able to run an update query that was already saved in Access using:

我能够使用以下方法运行已保存在 Access 中的更新查询：

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

This gave me errors until I replaced spaces in the query name with underscores in both the Access database and the execute statement.

这给了我错误，直到我在 Access 数据库和执行语句中用下划线替换了查询名称中的空格。

回答by Sixter

This is sort of a hack job, but you can query a query. That is, replace your sql string with the following:

这是一种黑客工作，但您可以查询查询。也就是说，用以下内容替换您的 sql 字符串：

sqlQuery = "SELECT * FROM QueryName;"

Before running this, one must ensure that the Access Database has been saved ie. press Ctrl+S (it is not sufficient that the query was run in Access).

在运行之前，必须确保 Access 数据库已保存，即。按 Ctrl+S（仅在 Access 中运行查询是不够的）。

回答by spinjector

Long time since this thread was created. If I understand it correctly, I might have something useful to add. I've given a name to what the OP describes, that being the process of using SQL from a query saved in an ACCDB to run in VBA via DAO or ADOBD. The name I've given it is "Object Property Provider", even with the acronym OPP in my notes, and for the object name prefix/suffix.

自创建此线程以来已经很长时间了。如果我理解正确，我可能会添加一些有用的东西。我已经为 OP 描述的内容命名，即使用保存在 ACCDB 中的查询中的 SQL 通过 DAO 或 ADOBD 在 VBA 中运行的过程。我给它起的名字是“对象属性提供者”，即使在我的笔记中使用首字母缩略词 OPP，以及对象名称的前缀/后缀。

The idea is an existing object in an ACCDB (usually a query) provides a property (usually SQL) that you need to use in VBA. I slapped together a function just to suck SQL out of queries for this; see below. Forewarning: sorry, but this is all in DAO, I don't have much use for ADODB. Hope you will still find the ideas useful.

这个想法是 ACCDB 中的现有对象（通常是查询）提供了您需要在 VBA 中使用的属性（通常是 SQL）。我拼凑了一个函数，只是为了从查询中提取 SQL；见下文。预警：抱歉，这一切都在 DAO 中，我对 ADODB 没有太大用处。希望你仍然会发现这些想法很有用。

I even went so far as to devise a method of using/inserting replaceable parameters in the SQL that comes from these OPP queries. Then I use VBA.Replace() to do the replacing before I use the SQL in VBA.

我什至设计了一种在来自这些 OPP 查询的 SQL 中使用/插入可替换参数的方法。然后我在 VBA 中使用 SQL 之前使用 VBA.Replace() 进行替换。

The DAO object path to the SQL of a query in an ACCDB is as follows:

ACCDB 中查询的 SQL 的 DAO 对象路径如下：

mySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL

The way I use replaceable parameters is by evaluating what needs to be replaced, and choosing an unusual name for the paramater that cannot possibly exist in the real database. For the most part, the only replacements I've made are field or table names, or the expressions of WHERE and HAVING clauses. So I name them things like "{ReplaceMe00000001}" and then use the Replace() function to do the work...

我使用可替换参数的方法是评估需要替换的内容，并为实际数据库中不可能存在的参数选择一个不寻常的名称。在大多数情况下，我所做的唯一替换是字段或表名，或者 WHERE 和 HAVING 子句的表达式。所以我将它们命名为“{ReplaceMe00000001}”，然后使用 Replace() 函数来完成工作......

sqlText = VBA.Replace(sqlText, "{ReplaceMe00000001}", "SomeActualParameter")

...and then use the sqlText in VBA. Here's a working example:

...然后在 VBA 中使用 sqlText。这是一个工作示例：

Public Function MySqlThing()
    Dim sqlText as String
    Dim myParamater as String
    Dim myExpression as String

    'Set everything up.
    sqlText = getSqlTextFromQuery("myQuery")
    myParameter = "{ReplaceMe00000001}"
    myExpression = "SomeDateOrSomething12/31/2017"

    'Do the replacement.
    sqlText = VBA.Replace(sqlText, myParameter, myExpression)

    'Then use the SQL.
    db.Execute sqlText, dbFailOnError
End Function

Function getSqlTextFromQuery(ByVal oppName As String) As String

    Dim app As Access.Application
    Dim db As DAO.Database
    Dim qdefs As DAO.QueryDefs
    Dim qdef As DAO.QueryDef
    Dim sqlText As String

    Set app = Access.Application
    Set db = app.CurrentDb
    Set qdefs = db.QueryDefs
    Set qdef = qdefs(oppName)

    oppGetSqlText = qdef.SQL

End Function