Microsoft Access VBA 编辑 SQL 查询

Microsoft Access VBA to edit SQL queries

提问by Gaioshin

I have an Access database with a single query in it. I can currently copy the query via VBA, using DoCmd.CopyObject. However, I need to be able to edit the SQL in each instance of the query individually. All the examples I have seen, however, involve recordset, which is something I am not using.

我有一个 Access 数据库，其中包含一个查询。我目前可以通过 VBA 复制查询，使用DoCmd.CopyObject. 但是，我需要能够单独编辑每个查询实例中的 SQL。但是，我所看到的所有示例都涉及记录集，而我没有使用它。

Any ideas?

有任何想法吗？

采纳答案by Sivakanesh

The question is a bit thin on what kind of query you want to edit, but say it is a select query that doesn't need any dynamic parameters.

这个问题对于您要编辑的查询类型有点单薄，但可以说它是一个不需要任何动态参数的选择查询。

Then, using the CopyObjectmethod, make a copy of the query as you did. Use the Catalogobject (you will need to reference the ADO Ext). Then, you can just change the SQL of the copy as below. Depending on your query, it may be a View or a Procedure, but a Select query should be listed as a View.

然后，使用该CopyObject方法，像您一样复制查询。使用Catalog对象（您将需要引用 ADO Ext）。然后，您只需更改副本的 SQL，如下所示。根据您的查询，它可能是视图或过程，但选择查询应列为视图。

Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Dim sQueryName As String
Dim sSQL As String

Set cn = CurrentProject.Connection
Set catDB = New ADOX.Catalog

catDB.ActiveConnection = cn
DoCmd.CopyObject , "Query1_c", acQuery, "Query1"
sQueryName = "Query1_c"
sSQL = "SELECT a,b,c FROM Table1"

Set cmd = New ADODB.Command
Set cmd = catDB.Views(sQueryName).Command
cmd.CommandText = sSQL
Set catDB.Views(sQueryName).Command = cmd

Set catDB = Nothing
cn.Close

回答by Ben McCormack

I do this kind of thing all the time! I've used VBA to rewrite Access queries as well as queries that "pass through" to a MySQL database. Rewriting a query in VBA depends on how complicated you want to get.

我经常做这种事！我使用 VBA 重写了 Access 查询以及“传递”到 MySQL 数据库的查询。在 VBA 中重写查询取决于您想要获得的复杂程度。

I've always used DAO, which is older technology(see comments below for clarification on DAO vs ADO), but it's worked for me in this instance. You'll need to add a reference to DAO in VBA by going to Tools > References, and then add "Microsoft DAO 3.6 Object Library."

我一直用DAO，这是较旧的技术（有关 DAO 与 ADO 的说明，请参阅下面的评论），但在这种情况下它对我有用。您需要通过转到“工具”>“引用”在 VBA 中添加对 DAO 的引用，然后添加“Microsoft DAO 3.6 对象库”。

You might write a procedure that looks like this:

您可能会编写如下所示的过程：

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb()
    Set qdf = db.QueryDefs(strQueryName)

    qdf.SQL = "SELECT [Table].Field1, [Table].Field2 " & vbCrLf & _
              "FROM [Table] " & vbCrLf & _
              "WHERE ([Table].Field1 = " & chr(34) & strParameter & chr(34) & ");"
End Sub

The above code is going to change the SQL for the query you specify with the query SQL in the VBA code with the strParameterembedded in the SQL query.

上面的代码将使用strParameter嵌入在 SQL 查询中的 VBA 代码中的查询 SQL 更改您指定的查询的 SQL。

Here's another way of doing it. This is code I used in a sales reporting application to rewrite a query based on a salesperson's number:

这是另一种方法。这是我在销售报告应用程序中使用的代码，用于根据销售人员的号码重写查询：

Sub rewriteAccountsBySalesRepSortSQL(lngSalesRep As Long)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strOriginal As String
    Dim strReplacePart As String
    Dim strSQLReplace As String
    Dim lngLocSalesperson As Long
    Dim lngLocMyRoundUp As Long
    Dim lngLocParen As Long
    Dim lngLocEndParen As Long

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryS Accounts by Sales Rep Sorted by Diff DESC")
    strSQL = qdf.SQL

    lngLocSalesperson = InStr(strSQL, "Salesperson1)=")
    lngLocEndParen = InStr(lngLocSalesperson + 14, strSQL, ")")
    strOriginal = Mid(strSQL, lngLocSalesperson, lngLocEndParen - lngLocSalesperson)
    'lngLocParen = InStrRev(strSQL, "(", lngLocSalesperson)

    strReplacePart = "Salesperson1)=" & lngSalesRep

    strSQLReplace = Replace(strSQL, strOriginal, strReplacePart)
    qdf.SQL = strSQLReplace
End Sub

Instead of writing out the whole query, the code simply finds the current sales rep number and replaces it with the new number. I actually prefer the latter method. The latter method was used to send a query directly to MySQL, so the syntax with an Access query may be slightly different.

该代码没有写出整个查询，而是简单地找到当前的销售代表编号并将其替换为新编号。我实际上更喜欢后一种方法。后一种方法用于直接向 MySQL 发送查询，因此 Access 查询的语法可能略有不同。

回答by David-W-Fenton

I would dissent from all the answers given here, even though they do address the question asked. I dissent because I think the question is based on incorrect assumptions -- there is no need to rewrite the SQL at all.

我不同意这里给出的所有答案，即使它们确实解决了所提出的问题。我反对是因为我认为这个问题是基于不正确的假设——根本不需要重写 SQL。

The question seems to assume that queries in Access have to be saved. They don't. You can execute any arbitrary SQL string at any time, either in code, or (for non-DML SQL) as the recordsource of a form or report. The SQL strings can be built on the fly and assigned as needed at runtime -- the only advantage of a saved QueryDef is if you need to use it in multiple locations.

这个问题似乎假设必须保存 Access 中的查询。他们没有。您可以随时在代码中或（对于非 DML SQL）作为表单或报表的记录源执行任意 SQL 字符串。SQL 字符串可以即时构建并在运行时根据需要分配——保存的 QueryDef 的唯一优点是如果您需要在多个位置使用它。

A saved QueryDef is basically the same as a VIEW in server databases.

保存的 QueryDef 与服务器数据库中的 VIEW 基本相同。

If the QueryDef has parameters, it's equivalent to a simple STORED PROCEDURE (i.e., ones that lack code, like IF/THEN or CASE SELECT branching).

如果 QueryDef 有参数，它就相当于一个简单的 STORED PROCEDURE（即那些缺少代码的，如 IF/THEN 或 CASE SELECT 分支）。

If you would implement the SQL as a VIEW on a server database, save it as a QueryDef in Access. If you'd do it as an SPROC in your server database, implement it as a saved parameter query.

如果要将 SQL 实现为服务器数据库上的 VIEW，请将其另存为 Access 中的 QueryDef。如果您将其作为服务器数据库中的 SPROC 来执行，请将其实现为保存的参数查询。

But first of all, determine if it needs to be saved at all.

但首先要确定它是否需要保存。

For what it's worth, I've been programming professionally in Access since 1996 and I generally don't save a lot of queries, and particularly don't save criteriain queries. Criteria are specific to runtime context and should be supplied at runtime, rather than saved in the QueryDef. I use saved QueryDefs for complex SQL that I need to re-use or for defining "views" (particularly those with complex joins) that are used in more than one place in the app.

值得一提的是，我从 1996 年以来一直在 Access 中进行专业编程，我通常不会保存很多查询，尤其是不要在查询中保存条件。标准特定于运行时上下文，应在运行时提供，而不是保存在 QueryDef 中。我将保存的 QueryDefs 用于需要重复使用的复杂 SQL，或用于定义在应用程序中不止一处使用的“视图”（尤其是那些具有复杂连接的视图）。

The original question does not identify the context in which changing the criteria is needed, so it's really impossible to suggest the best approach. This is a case where I would fault the question for foreclosing proper discussion as it proposes a specific SOLUTION and asks how to implement it, instead of describing the PROBLEM and asking for the range of workable solutions. In order to do the latter, we'd need to know about the context (is the SQL DML or a SELECT? is it being used in code or as the recordsource for a form or report? etc.), but that's completely lacking here, so a full range of solutions is never going to be offered.

最初的问题没有确定需要更改标准的上下文，因此真的不可能提出最佳方法。在这种情况下，我会错误地排除适当讨论的问题，因为它提出了一个特定的解决方案并询问如何实施它，而不是描述问题并询问可行解决方案的范围。为了实现后者，我们需要了解上下文（是 SQL DML 还是 SELECT？它是在代码中使用还是作为表单或报告的记录源？等），但这里完全缺乏，因此永远不会提供全方位的解决方案。