确保 Access 2000 mdb 上的 SQL-92 到 Access 2010 下的 SQL Server [英] Ensure SQL-92 on Access 2000 mdb to SQL Server under Access 2010

问题描述

由于我无法完全控制的情况，我必须开发 ACCESS 查询，这些查询将存储在 Access 2000 MDB 中，但可以在以下任何一种下开发:Access 2000、2002、2003、2007、2010.这些查询将访问(通过链接表)MS SQL Server DB 或 Access 2000 .MDB 文件.

Due to circumstances not fully under my control, I have to develop ACCESS queries which will be stored in an Access 2000 MDB but which may be developed under any one of: Access 2000, 2002, 2003, 2007, 2010. These queries will access (via linked tables) either an MS SQL Server DB or an Access 2000 .MDB file.

过去，我们发现查询不兼容 ANSI-92 时遇到了问题.我们通过标记工具|选项对话框的表/查询选项卡中的复选框解决了这个问题(在 2007 之前版本的 Access 下).

In the past, we had issues which we tracked down to queries not being ANSI-92 compatible. This we solved by marking the Check Box in the Tables/Queries tab of the Tools|Options dialog (under older versions of Access than 2007).

我们在各种系统之间移动这些 Access 应用程序 .MDB，现在我们通常没有任何问题.

We move these Access application .MDBs between various systems and we now generally don't have any problems.

但是，我刚刚开发了一个适用于所有内部连接的查询，但是当我将其中一个连接更改为外部连接时，查询失败并在 Access 2010 下出现无效操作"错误.所以我想我会检查是否仍然设置了 ANSI-92 复选框.我找不到.

However, I've just developed a query which works fine with all inner joins, but when I change one of the joins to an outer join, the query fails with an "Invalid Operation" error under Access 2010. So I thought I'd check if the ANSI-92 checkbox was still set. I couldn't find it.

现在，我知道 Access 2003 及更高版本会自动使用 ANSI 92 语法，但我想知道是否有任何方法可以检查这些查询在这种情况下(在特定情况下)是否如此.我观察到当我们更改复选框时，我所有的Like"查询都变成了Alike"查询......

Now, I KNOW that Access 2003 and later uses ANSI 92 syntax automatically but I wondered if there's any way to check that this is the case for these queries in this (rather specific circumstances). I observed that when we changed the checkbox, all my "Like" queries became "ALike" queries...

所以问题归结为 ANSI 92 语法在这种情况下是完全透明的，而我的查询失败的原因是别的......

So the question boils down to is ANSI 92 syntax totally transparent in this circumstances and the reason my query is failing is something else...

TIA，保罗

推荐答案

在 Access 2010 中，SQL Server 语法兼容性的设置位于:

In Access 2010, the setting for SQL Server Syntax compatibility is under:

文件 > 选项 > 对象设计器 > 查询设计.

File > Options > Object Designers > Query Design.

您首先需要检查是否设置了该选项，以确认您的查询行为是否正常.

You'll first need to check if that option is set or not to confirm whether the behaviour of your query is normal.

至于LIKE vs ALIKE，后者将以ANSI方式工作，使用%和_> 模式匹配.
在 SQL-89 兼容模式(MS Access SQL 子集)中，LIKE 仅适用于 * 和 ? 模式，但如果您将兼容性更改为 SQL-92，则需要改用 % 和 _ 重写您的查询.

As for LIKE vs ALIKE, the latter will work in the ANSI-way, using % and _ pattern matching.
In SQL-89 compatibility mode (the MS Access SQL subset), LIKE only works with * and ? patterns but if you change the compatibility to SQL-92, you need to rewrite your queries with % and _ instead.

这就是引入 ALIKE 的原因，它允许您编写符合 ANSI 的模式，而不管您选择的兼容级别如何(因此在 SQL-92 兼容模式下，两者的行为相同).

That's why ALIKE was introduced, it allows you to write ANSI compliant patterns regardless of the compatibility level you choose (so in SQL-92 compatibility mode, both are behaving the same).

这篇关于确保 Access 2000 mdb 上的 SQL-92 到 Access 2010 下的 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！