确保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数据库或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不兼容的查询.我们通过在工具|选项"对话框的表/查询"选项卡中选中复选框来解决此问题(在Access的早期版本中是2007年以下的版本).

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语法，但我想知道是否有任何方法可以在这种情况下(相对于特定情况)检查这些查询是否属于这种情况.我观察到，当我们更改复选框时，我所有的赞"查询都变成了"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与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屋！