JET SQL：LEFT JOINS 和 RIGHT JOINS 只能在 JET 4.0 及更高版本下工作吗？
以下文章是否适用于 JET 的所有使用（包括通过 DAO 或 OLEDB 在客户端应用程序中使用）：
http: //support.microsoft.com/kb/275058
这是否意味着 JET 4.0 或更高版本对于涉及 Access MDB 的任何内容都是强制性的？
Does the following article apply to all uses of JET (including from within client apps via DAO or OLEDB):
http://support.microsoft.com/kb/275058
Does this mean JET 4.0 or above is mandatory for anything involving an Access MDB?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不幸的是，知识库文章的措辞很糟糕。它们引用的 SQL 92 语法是在 OUTER 连接的 ON 子句中添加非列比较过滤器时的情况（这也意味着它在 QBE 中不可见）。因此，带有与查询中的表进行标准列比较的标准外连接（LEFT 或 RIGHT）将在 Jet 的所有版本中正常工作。当您尝试在 OUTER 连接的 ON 子句中添加列比较过滤器以外的内容（例如 [Col] > 200）时，Access 无法正确解释 SQL 语句。
我个人也遇到过这个问题，简而言之，仅仅因为它是标准 SQL 并不意味着 Access 会返回正确的结果。这是许多人对 Access 感到沮丧的众多事情之一。
补充 要回答您的最后一个问题，不，对于使用 Access 开发的应用程序，不强制使用 Jet 作为后端存储技术。您可以创建使用 SQL Server 或其他数据库产品作为后端数据库存储的 Access 解决方案。
关于 Jet Engine 的维基百科文章实际上非常全面地描述了差异和功能。
Unfortunately, the KB article is worded poorly. The SQL 92 syntax to which they refer is when you add a non-column comparison filter in the ON clause of an OUTER join (which also means it will not be viewable in the QBE). Thus, a standard outer join (LEFT or RIGHT) with standard column comparisons to tables in the query will work just fine in all versions of Jet. It is when you try to add something other than column comparison filter (e.g. [Col] > 200) in the ON clause of an OUTER join that Access does not properly interpret the SQL statement.
I have personally run into this very issue, In short, just because it is standard SQL does not mean that Access will return the proper results. It is one of the many things that many people find frustrating about Access.
ADDITION To answer your last question, no, it is not mandatory to use Jet as the backend storage technology for an application developed with Access. You can create Access solutions that use SQL Server or other database products as the backend database store.
The Wikipedia article on the Jet Engine is actually pretty thorough in describing the differences and features.
编辑：
我刚刚重读了知识库文章，它说问题出在 Jet 4.0 Service Pack 3 及更早版本上 - 这是很久很久以前就被取代的 Jet 版本。任何地方都不应该有 Windows 2000 或更高版本的计算机打开了 Windows Update 或自 c. 以来已更新过。 2002 年，拥有 Service Pack 3 之前的任何 Jet 版本。要检查计算机上的 Jet 版本，请查找 MSJET40.DLL。内部版本号以服务包编号开头。在这台电脑上，我的版本号是 4.0.9511.0，所以是 SP9。
如果您的 MSJET40.DLL 版本号为 4.0.3XXX.X 或更早版本，请按照获取服务包的说明进行操作，问题将会得到解决。但我认为市场上仍然安装 Jet 4.0 SP3 或更早版本的 Windows PC 数量并不多。而且只有未使用 Windows Update 进行更新的电脑才可能拥有它。
原答案：
Jet 4.0 于 1999 年随 Access 2000 一起发布。它包含在 Windows 2000 中，并且包含在此后的每个 Windows 版本中。因为它是一个操作系统组件（它用于与 Active Directory 数据存储交互，尽管 AD 使用其他 Jet 引擎，而不是 Access 使用的引擎）。 Jet 4.0 是 Jet 的冻结版本，Windows 团队（拥有其代码）不会进一步开发。
当 Access 2007 发布时，创建了新版本的 Jet 数据库引擎，专用于 Access 开发团队，称为 ACE。它实际上是 Jet 5.0（或 4.5，具体取决于您认为增强功能的主要程度），但它引入了一种新的文件格式 ACCDB。
您引用的文章说它仅适用于 Access 2002 和 Access 2003，但我认为由于这是 Jet 4.0 问题，因此它也适用于 Access 2000。同样，它也适用于从 Access 外部使用 Jet 4.0 ，例如 OLEDB 或 ODBC。
请注意，通过 DAO，您不会遇到该问题，因为 DAO 无法使用 SQL 92 兼容模式。 OLEDB 总是会出现此问题，因为 OLEDB 的 Jet 驱动程序始终使用 SQL 92 模式。
我不清楚如果使用 ACE 查询 MDB 是否可以避免该问题。
EDIT:
I just reread the KB article, and it says the problem is with Jet 4.0 Service Pack 3 and earlier -- that's a version of Jet that has long, long ago been superseded. There should be no Windows 2000 or later computers anywhere that have Windows Update turned on or that have been updated since c. 2002 that have any version of Jet as old as service pack 3. To check the version of Jet on your computer, find MSJET40.DLL. The build number begins with the service pack number. On this PC, I have version number 4.0.9511.0, so that's SP9.
If you have an MSJET40.DLL with version number 4.0.3XXX.X or earlier, then follow the instructions on getting the service pack, and the problem will be resolved. But I don't believe there's any signficant number of Windows PCs out there that still have Jet 4.0 SP3 or earlier on them. And only PCs that aren't being updated with Windows Update could possibly have it.
ORIGINAL ANSWER:
Jet 4.0 was released in 1999 with Access 2000. It was included in in Windows 2000 and is included in every version of Windows since. Because it's an OS component (it is used for interaction with the Active Directory data store, though AD uses the other Jet engine, not the one used by Access). Jet 4.0 is a frozen version of Jet and will not be developed any further by the Windows team (who owns the code for it).
When Access 2007 was released, a new version of the Jet database engine was created, private to the Access development team, and called ACE. It is really Jet 5.0 (or 4.5, depending on how major you consider the enhancements to be), but it introduced a new file format, ACCDB.
The article you cite says it applies to only Access 2002 and Access 2003, but I'd think that since it's a Jet 4.0 issue, it would also apply to Access 2000. Likewise, it would apply to any use of Jet 4.0 from outside Access, e.g., OLEDB or ODBC.
Note that via DAO, you can't encounter the problem, because DAO cannot use SQL 92 compatibility mode. The problem will always occur with OLEDB because the Jet driver for OLEDB always uses SQL 92 mode.
It's not clear to me if the problem is avoided if you use ACE to query an MDB.
JET 是 Access 使用的数据库引擎。或者换句话说，Access 是一个基于 JET 数据库引擎构建的应用程序。 （Access 不必使用 JET，它还可以直接连接到 SQL Server。）
每当您对 .mdb 文件执行查询时，无论技术如何，执行查询的都是 JET 引擎。
访问 .mdb 需要 JET 版本，但是否必须为 4.0 将取决于 .MDB 文件版本以及早期版本中是否存在需要避免的普遍错误。但总的来说，是的，您应该期望将最新的 JET 版本与 Access MDB 一起使用。
OUTER JOIN 适用于旧版本 - 提到的错误仅在还有 WHERE 子句时才普遍存在。
JET is the database engine used by Access. Or put it another way, Access is an app built on the JET database engine. (Access doesn't have to use JET, it can also connect directly to SQL server.)
When ever you execute queries against an .mdb file, regardless of technology, it is the JET engine that executes the query.
A version of JET is needed to access a .mdb, but wether it has to be 4.0 will depend upon the .MDB file version and whether there are prevalent bugs in earlier versions that you need to avoid. But in general, yes, you should expect to use the latest JET version with an Access MDB.
OUTER JOINs work on older versions - the bug mentioned is only prevalent when there is also a WHERE clause.