在 MS Access 中管理和调试 SQL 查询

Managing and debugging SQL queries in MS Access

提问by Renaud Bompuis

MS Access has limited capabilities to manage raw SQL queries: the editor is quite bad, no syntax highlighting, it reformats your raw SQL into a long string and you can't insert comments.

MS Access 管理原始 SQL 查询的功能有限：编辑器非常糟糕，没有语法突出显示，它将原始 SQL 重新格式化为长字符串，并且您无法插入注释。

Debugging complex SQL queries is a pain as well: either you have to split it into many smaller queries that become difficult to manage when your schema changes or you end-up with a giant query that is a nightmare to debug and update.

调试复杂的 SQL 查询也很痛苦：要么您必须将其拆分为许多较小的查询，这些查询在架构更改时变得难以管理，要么最终得到一个巨大的查询，这对于调试和更新来说是一场噩梦。

How do you manage your complex SQL queries in MS Access and how do you debug them?

您如何在 MS Access 中管理复杂的 SQL 查询以及如何调试它们？

Edit
At the moment, I'm mostly just using Notepad++for some syntax colouring and SQL Pretty Printerfor reformatting sensibly the raw SQL from Access.
Using an external repository is useful but keeping there's always the risk of getting the two versions out of sync and you still have to remove comments before trying the query in Access...

编辑
目前，我主要使用Notepad++进行一些语法着色，使用SQL Pretty Printer重新格式化来自 Access 的原始 SQL。
使用外部存储库很有用，但始终存在使两个版本不同步的风险，并且您仍然必须在尝试在 Access 中进行查询之前删除注释...

回答by recursive

For debugging, I edit them in a separate text editor that lets me format them sensibly. When I find I need to make changes, I edit the version in the text editor, and paste it back to Access, never editing the version in Access.

为了调试，我在一个单独的文本编辑器中编辑它们，让我合理地格式化它们。当我发现需要进行更改时，我会在文本编辑器中编辑该版本，然后将其粘贴回 Access，而从不在 Access 中编辑该版本。

Still a major PITA.

仍然是主要的 PITA。

回答by Smandoli

I have a few tips that are specific to SQL in VBA.

我有一些特定于 VBA 中 SQL 的技巧。

Put your SQL code with a string variable. I used to do this:

将您的 SQL 代码与字符串变量放在一起。我曾经这样做过：

DoCmd.RunSQL "SELECT ..."

That is hard to manage. Do this instead:

那很难管理。改为这样做：

strSQL = "SELECT ..."
DoCmd.RunSQL strSQL

Often you can't fix a query unless you see just what's being run. To do that, dump your SQL to the Immediate Window just before execution:

通常，除非您看到正在运行的内容，否则您无法修复查询。为此，请在执行前将您的 SQL 转储到立即窗口：

strSQL = "SELECT ..."
Debug.Print strSQL
Stop
DoCmd.RunSQL strSQL

Paste the result into Access' standard query builder (you must use SQL view). Now you can test the final version, including code-handled variables.

将结果粘贴到 Access 的标准查询构建器中（您必须使用SQL 视图）。现在您可以测试最终版本，包括代码处理的变量。

When you are preparing a long query as a string, break up your code:

当你准备一个长查询作为一个字符串时，分解你的代码：

strSQL = "SELECT wazzle FROM bamsploot" _
      & vbCrLf & "WHERE plumsnooker = 0"

I first learned to use vbCrLfwhen I wanted to prettify long messages to the user. Later I found it makes SQL more readable while coding, and it improves the output from Debug.Print. (Tiny other benefit: no space needed at end of each line. The new line syntax builds that in.)

vbCrLf当我想美化给用户的长消息时，我第一次学会了使用。后来我发现它使 SQL 在编码时更具可读性，并且它改进了Debug.Print. （还有一个小好处：每行末尾不需要空格。新行语法构建了这一点。）

(NOTE: You might think this will let you add add comments to the right of the SQL lines. Prepare for disappointment.)

（注意：您可能认为这会让您在 SQL 行的右侧添加注释。准备好失望吧。）

As said elsewhere here, trips to a text editor are a time-saver. Some text editors provide better syntax highlighting than the official VBA editor. (Heck, StackOverflow does better.) It's also efficient for deleting Access cruft like superfluous table references and piles of parentheses in the WHERE clause.

正如此处其他地方所说，使用文本编辑器可以节省时间。一些文本编辑器提供比官方 VBA 编辑器更好的语法突出显示。（哎呀，StackOverflow 做得更好。）删除 Access cruft 也很有效，例如多余的表引用和 WHERE 子句中的成堆括号。

Work flow for serious trouble shooting:

解决严重故障的工作流程：

VBA Debug.Print >       (capture query during code operation)
  query builder   >     (testing lab to find issues)
     Notepad++      >   (text editor for clean-up and review)
  query builder   >     (checking, troubleshooting) 
VBA

Of course, trouble shooting is usually a matter of reducing the complexity of a query until you're able to isolate the problem (or at least make it disappear!). Then you can build it back up to the masterpiece you wanted. Because it can take several cycles to solve a sticky problem, you are likely to use this work flow repeatedly.

当然，故障排除通常是降低查询的复杂性，直到您能够隔离问题（或至少使其消失！）。然后，您可以将其重新构建为您想要的杰作。因为解决一个棘手的问题可能需要几个周期，所以您可能会重复使用此工作流程。

回答by transistor1

I wrote Access SQL Editor-- an Add-In for Microsoft Access-- because I write quite a lot of pass-through queries, and more complex SQL within Access. This add-in has the advantage of being able to store formatted SQL (with comments!) within your Access application itself. When queries are copied to a new Access application, formatting is retained. When the built-in editor clobbers your formatting, the tool will show your original query and notify you of the difference.

我编写了Access SQL 编辑器——Microsoft Access 的一个插件——因为我在 Access 中编写了相当多的传递查询和更复杂的 SQL。此加载项的优势在于能够在您的 Access 应用程序本身中存储格式化的 SQL（带注释！）。将查询复制到新的 Access 应用程序时，会保留格式。当内置编辑器破坏您的格式时，该工具将显示您的原始查询并通知您差异。

It currently does not debug; if there was enough interest, I would pursue this-- but for the time being the feature set is intentionally kept small.

目前不调试；如果有足够的兴趣，我会追求这个——但目前功能集是有意保持较小的。

It is not free for the time being, but purchasinga license is very cheap. If you can't afford it, you can contact me. There is a free 14-day trial here.

它暂时不是免费的，但购买许可证非常便宜。如果你买不起，你可以联系我。此处提供 14 天免费试用。

Once it's installed, you can access it through your Add-Ins menu (In Access 2010 it's Database Tools->Add Ins).

安装后，您可以通过“加载项”菜单访问它（在 Access 2010 中，它是“数据库工具”->“加载项”）。

回答by eksortso

Debugging is more of a challenge. If a single column is off, that's usually pretty easy to fix. But I'm assuming you have more complex debugging tasks that you need to perform.

调试更具挑战性。如果单个列关闭，通常很容易修复。但我假设您需要执行更复杂的调试任务。

When flummoxed, I typically start debugging with the FROMclause. I trace back to all the tables and sub-queries that comprise the larger query, and make sure that the joins are properly defined.

当感到困惑时，我通常会使用该FROM子句开始调试。我回溯到构成较大查询的所有表和子查询，并确保正确定义了连接。

Then I check my WHEREclause. I run lots of simple queries on the tables, and on the sub-queries that I've already checked or that I already trust, and make sure that when I run the larger query, I'm getting what I expect with the WHEREconditions in place. I double-check the JOINconditions at the same time.

然后我检查我的WHERE条款。我对表和我已经检查过或我已经信任的子查询运行了很多简单的查询，并确保当我运行更大的查询时，我得到了我期望的WHERE条件地方。我同时仔细检查JOIN条件。

I double-check my column definitions to make sure I'm retrieving what I really want to see, especially if the formulas involved are complicated. If you have something complicated like a coordinated subquery in a column definition

我仔细检查了我的列定义，以确保我正在检索我真正想要看到的内容，尤其是如果所涉及的公式很复杂。如果您有一些复杂的东西，例如列定义中的协调子查询

Then I check to see if I'm grouping data properly, making sure that "DISTINCT"'s and "UNION"'s without UNION ALLdon't remove necessary duplicates.

然后我检查我是否正确地对数据进行了分组，确保“ DISTINCT”和“ UNION”UNION ALL没有删除必要的重复项。

I don't think I've ever encountered a SQL query that couldn't be broken down this way. I'm not always as methodical as this, but it's a good way to start breaking down a real stumper.

我认为我从未遇到过不能以这种方式分解的 SQL 查询。我并不总是像这样有条不紊，但这是开始打破真正绊脚石的好方法。

---

---

One thing I could recommend when you write your queries is this: Never use SELECT *in production code.Selecting all columns this way is a maintenance nightmare, and it leads to big problems when your underlying schemas change. You should always write out each and every column if you're writing SQL code that you'll be maintaining in the future. I saved myself a lot of time and worry just by getting rid of "SELECT *"'s in my projects.

当您编写查询时，我可以推荐的一件事是：永远不要SELECT *在生产代码中使用。以这种方式选择所有列是一个维护噩梦，当您的底层架构发生变化时，它会导致大问题。如果您正在编写将来要维护的 SQL 代码，则应始终写出每一列。通过SELECT *在我的项目中摆脱“ ”，我为自己节省了很多时间和担心。

The downside to this is that those extra columns won't appear automatically in queries that refer to "SELECT *" queries. But you should be aware of how your queries are related to each other, anyway, and if you need the extra columns, you can go back and add them.

这样做的缺点是那些额外的列不会自动出现在引用“ SELECT *”查询的查询中。但无论如何，您应该了解您的查询之间是如何关联的，如果您需要额外的列，您可以返回并添加它们。

---

---

There is some hassle involved in maintaining a code repository, but if you have versioning software, the hassle is more than worth it. I've heard of ways of versioning SQL code written in Access databases, but unfortunately, I've never used them.

维护代码存储库涉及一些麻烦，但如果您有版本控制软件，那么麻烦就值得了。我听说过对用 Access 数据库编写的 SQL 代码进行版本控制的方法，但不幸的是，我从未使用过它们。

回答by EdgarVerona

If you're doing really complex queries in MS Access, I would consider keeping a repository of those queries somewhere outside of the Access database itself... for instance, in a .sql file that you can then edit in an editor like Intype that will provide syntax highlighting. It'll require you to update queries in both places, but you may end up finding it handy to have an "official" spot for it that is formatted and highlighted correctly.

如果您在 MS Access 中进行非常复杂的查询，我会考虑将这些查询的存储库保存在 Access 数据库本身之外的某个地方……例如，在一个 .sql 文件中，然后您可以在像 Intype 这样的编辑器中进行编辑将提供语法高亮。这将要求您在两个地方更新查询，但您最终可能会发现为它提供一个格式正确并突出显示的“官方”位置很方便。

Or, if at all possible, switch to SQL Server 2005 Express Edition, which is also free and will provide you the features you desire through the SQL Management Studio (also free).

或者，如果可能，切换到同样免费的 SQL Server 2005 Express Edition，它将通过 SQL Management Studio（同样免费）为您提供所需的功能。

回答by Steve

Expanding on this suggestion from Smandoli:

扩展来自 Smandoli 的这个建议：

NO:   DoCmd.RunSQL ("SELECT ...")
YES:  strSQL = "SELECT ..."
      DoCmd.RunSQL (strSQL)

If you want to keep the SQL code in an external file, for editing with your favorite text editor (with syntax coloring and all that), you could do something like this pseudo-code:

如果您想将 SQL 代码保存在外部文件中，以便使用您喜欢的文本编辑器（带有语法着色等）进行编辑，您可以执行以下伪代码：

// On initialization:
global strSQL
f = open("strSQL.sql")
strSQL = read_all(f)
close(f)

// To to the select:
DoCmd.RunSQL(strSQL)

This may be a bit clunky -- maybe a lot clunky -- but it avoids the consistency issues of edit-copy-paste.

这可能有点笨拙——也许非常笨拙——但它避免了编辑-复制-粘贴的一致性问题。

Obviously this doesn't directly address debugging SQL, but managing code in a readable way is a part of the problem.

显然，这并不能直接解决调试 SQL 的问题，但以可读的方式管理代码是问题的一部分。

回答by Philippe Grondier

Are you talking here about what MS-Access calls 'queries' and SQL call 'views' or about the 'MS-Access pass-through' queries which are SQL queries? Someone could get easily lost! My solution is the following

您在这里谈论的是 MS-Access 所谓的“查询”和 SQL 调用“视图”，还是 SQL 查询中的“MS-Access 传递”查询？有人很容易迷路！我的解决方案如下

1. free SQL Server Management Studio Express, where I will elaborate and test my queries
2. a query table on the client side, with one field for the query name (id_Query) and another one (queryText, memo type) for the query itself.

1. 免费的 SQL Server Management Studio Express，我将在其中详细说明和测试我的查询
2. 客户端的查询表，其中一个字段用于查询名称 ( id_Query)，另一个字段( queryText, 备忘录类型) 用于查询本身。

I then have a small function getSQLQueryin my VBA code to be used when I need to execute a query (either returning a recordset or not):

然后，getSQLQuery当我需要执行查询（返回或不返回记录集）时，我的 VBA 代码中有一个小函数可以使用：

Dim myQuery as string, _
    rsADO as ADODB.recorset

rsADO = new ADODB.recordset
myQuery = getSQLQuery(myId_Query)

'if my query retunrs a recordset'
set rsADO = myADOConnection.Execute myQuery
'or, if no recordset is to be returned'
myADOConnection.Execute myQuery

For views, it is even possible to keep them on the server side and to refer to them from the client side

对于视图，甚至可以将它们保留在服务器端并从客户端引用它们

set rsADO = myADOConnection.execute "dbo.myViewName"

回答by eksortso

Similar to recursive,I use an external editor to write my queries. I use Notepad++ with the Light Explorer extension for maintaining several scripts at a time, and Notepad2 for one-off scripts. (I'm kind of partial to Scintilla-based editors.)

与递归类似，我使用外部编辑器来编写查询。我将 Notepad++ 与 Light Explorer 扩展一起用于一次维护多个脚本，将 Notepad2 用于一次性脚本。（我有点偏爱基于 Scintilla 的编辑器。）

Another option is to use the free SQL Server Management Studio Express, which comes with SQL Server Express. (EDIT: Sorry, EdgarVerona, I didn't notice you mentioned this already!)I normally use it to write SQL queries instead of using Access, because I typically use ODBC to link to a SQL Server back end anyway. Beware that the differences in the syntax of T-SQL, used by SQL Server, and Jet SQL, used by Access MDB's, are sometimes substantial.

另一种选择是使用 SQL Server Express 附带的免费 SQL Server Management Studio Express。（编辑：对不起，EdgarVerona，我没有注意到你已经提到了这一点！）我通常使用它来编写 SQL 查询而不是使用 Access，因为我通常使用 ODBC 来链接到 SQL Server 后端。请注意，SQL Server 使用的 T-SQL 和 Access MDB 使用的 Jet SQL 的语法有时差异很大。

回答by John

Well to my knowledge there are 2 options:

据我所知，有两种选择：

• Notepad++ with Poor man's t-sql formatter plugin ..i know there is already a mention for SQL Pretty Printer but i haven't used it..so my workflow is ..i create the query in Access..i copy paste it to Notepad++ ...i format it..i work on it ...back to Access..only issue..it pads in some cases spaces in this case : [Forms]![AForm].[Ctrl] and they become [Forms] ! [AForm].[Ctrl] but i am used to and it doesn't bother me..
• SoftTree SQL Assistant (http://www.softtreetech.com/sqlassist/index.htm) bring just about everything you wanted on a SQL editor...i have worked a bit in the past(trial) but its price tag is a bit stiff

• Notepad++ 带有穷人的 t-sql 格式化程序插件..我知道已经提到了 SQL Pretty Printer 但我没有使用过它..所以我的工作流程是..我在 Access 中创建查询..我将其复制粘贴到记事本++ ...我对其进行格式化...我正在处理它...返回访问...仅问题...在某些情况下它会填充空格在这种情况下：[Forms]！[AForm].[Ctrl] 并且它们变成 [形式] ！[AForm].[Ctrl] 但我已经习惯了，它不打扰我..
• SoftTree SQL 助手 ( http://www.softtreetech.com/sqlassist/index.htm) 在 SQL 编辑器上带来了您想要的一切......我过去工作过一些（试用）但它的价格标签是有点僵硬