MS Access 链接表中的可空布尔字段

看起来我不是唯一一个遇到此问题的人，但似乎没有解决此问题的方法.

我在 Access 2010 中工作，使用链接表到 SQL Server 2005 数据库(通过 SQL Server ODBC 管道).在该表中，其中一个布尔字段被标记为可为空，并且该表中的几条记录实际上在该字段中具有空值.到目前为止一切顺利.

进入 Access，一旦您打开链接表，Access 就会显示 0(假)而不是空白单元格(问题 1).如果您尝试修改记录中的任何内容，您会收到一条错误消息，指出该记录已被其他人修改，并且您的更改无法保存.最后一个问题是由于 Access 不容忍可为空的 bool 字段，并且在尝试保存值时有点疯狂.

我的研究表明，这可能与 Access 在后台使用 Jet 连接到 SQL Server 数据库有关，而 Jet 显然不支持可为空的布尔值.似乎没有一种方法可以配置 Jet 来支持这一点(尽管可能有，如果您在代码中连接).我还认为 MS 正在用 Office 2010 中使用的另一种技术(我认为是 ACE)替换 Jet，但无法确定这是否是 Access 实际使用的技术.无论哪种情况，我都找不到关于可空布尔值的可配置选项.

最后，这个问题似乎在不久前就被提交给了 MS，但他们最终没有答案:https://connect.microsoft.com/SQLServer/feedback/details/617339/null-bit-fields-produce-spurious-ms-access-errors-when-using-the-native-odbc-driver?wa=wsignin1.0#tabs

我想知道是否有其他人遇到过这个问题并找到了解决方案.在您提出建议之前，关闭可为空选项并将所有空值设置为 'false' 在我们的案例中并不是一个真正的选择.对我们来说，null 实际上是一个有效状态，与 'false 非常不同.

谢谢！

解决方案

ACE 是 Jet 的升级版(从 Jet 4.0 代码库分叉出来，由 Windows 团队维护，没有看到任何进一步的开发，而 ACE 已满由 Access 团队开发).它与 Jet 没有太大区别，只是它是数据库引擎的新版本，并且具有 Jet 所缺乏的功能.

可空布尔值不是添加的功能之一.在任何情况下，如果我没记错的话，关于布尔值是否应该为 Nullable 以及 Jet/ACE 是否应该为 Nullable 存在很大的理论争论.

即使在 Access/Jet/ACE 中，不可为空的布尔值也会导致问题(Allen Browne 讨论过这样一个问题，与左连接).我的建议是，您将该字段更改为 Nullable Bit、Byte 或 Integer 字段(我不确定 SQL Server 中的确切数据类型，也不确定哪种类型与 Access/Jet/ACE 最兼容).

或者，您可以通过使用视图将服务器端布尔值 CAST() 转换为 INT，以处理 BIGINT 问题的方式来处理它.这使其不可编辑，但(与 BIGINT 一样)，您可以将原始字段保留在 VIEW 中并使用适当的值写入该字段，而 CAST() 版本仅用于显示.

就其价值而言，SSMA for Access 将 Jet/ACE 布尔值提升为可空位字段(但不确定为什么它们可以为空——我可能需要检查我的一些应用程序以确保它们正常工作！).

Looks like I'm not the only one out there with this issue, but there doesn't seem to be an anwwer to this problem.

I'm working in Access 2010, using a linked table to an SQL Server 2005 database (through an SQL Server ODBC pipe). In that table, one of the boolean fields is marked as nullable, and several records in this table do in fact have a null in the field. So far so good.

In comes Access, and as soon as you open the linked table, Access shows a 0 (false) instead of a blank cell (problem #1). And if you try to modify anything in the record, you get an error message saying the record was modified by someone else and your changes cannot be saved. This last problem is due to the fact that Access doesn't tolerate nullable bool fields, and goes a bit nuts when trying to save the value.

My research shows that this could have something to do with Access using Jet in the background to connect to the SQL Server database, and Jet apparently does not support nullable bools. There doesn't seem to be a way to configure Jet to support this (although perhaps there is, if you're connecting in code). I also thought MS was replacing Jet with another technology used in Office 2010 (ACE, I think), but cannot tell if this is what's actually being used by Access. In either case, I can find no configurable options regarding nullable bools.

Finally, this issue seems to have been brought up to MS a short while ago, but there's no answer on their end: https://connect.microsoft.com/SQLServer/feedback/details/617339/null-bit-fields-produce-spurious-ms-access-errors-when-using-the-native-odbc-driver?wa=wsignin1.0#tabs

I'm wondering if anyone else out there has run into this and found a solution. And before you suggest it, taking the nullable option off and setting all nulls to 'false' is not really an option in our case. For us, null is actually a valid state and very different from 'false.

Thx!

解决方案

ACE is an upgrade of Jet (forked from the Jet 4.0 codebase, which is maintained by the Windows team and not seeing any further development, while ACE is under full development by the Access team). It's not significantly different from Jet, except in that it's a new version of the database engine and has features that Jet lacked.

Nullable Booleans are not one of the added features. In any case, if I'm not mistaken there are big theoretical arguments about whether Booleans should be Nullable and Jet/ACE comes down on the side that says they shouldn't be.

Non-nullable Booleans cause problems even within Access/Jet/ACE (Allen Browne has discussed one such, with LEFT JOINs). My suggestion is that you change the field to a Nullable Bit, Byte or Integer field (I'm not sure what exact data types are in SQL Server, nor what is going to be most compatible with Access/Jet/ACE).

Alternatively, you can approach it the way the BIGINT problem is dealt with by using a view to CAST() the server-side Boolean to an INT. That makes it non-editable but (as with BIGINT), you can keep the original field in the VIEW and write to that with appropriate values, while the CAST() version is for display only.

For what it's worth, the SSMA for Access upsizes Jet/ACE Booleans to nullable bit fields (not sure why they are Nullable, though -- I may need to check some of my apps to make sure they are working correctly!).