MS Access链接表中的可空布尔字段 [英] Nullable bool fields in MS Access linked tables
问题描述
看起来我不是唯一一个解决此问题的人，但似乎并没有解决这个问题.
我正在Access 2010中工作，使用到SQL Server 2005数据库的链接表(通过SQL Server ODBC管道).在该表中，布尔值字段之一被标记为可为空，并且该表中的几条记录实际上在该字段中为空.到目前为止一切顺利.
Access来了，一旦打开链接表，Access就会显示0(假)而不是空白单元格(问题1).而且，如果您尝试修改记录中的任何内容，则会收到一条错误消息，指出该记录已被其他人修改，并且您的更改无法保存.最后一个问题是由于Access不能容忍可为null的布尔字段，并且在尝试保存该值时有点胡言乱语.
我的研究表明，这可能与Access在后台使用Jet连接到SQL Server数据库有关，并且Jet显然不支持可空布尔.似乎没有一种方法可以配置Jet来支持此功能(尽管如果您使用代码进行连接，也许可以).我还认为MS正在用Office 2010中使用的另一种技术(我认为是ACE)代替Jet，但是无法确定这是否是Access实际使用的东西.无论哪种情况，我都找不到关于可空布尔的可配置选项.
最后，这个问题似乎已经在不久前被提交给MS了，但是他们的答案没有答案:
谢谢！ ACE是Jet的升级版(由Jet 4.0代码库提供，该代码库由Windows团队维护，并且在ACE处于完全运行状态时未见任何进一步的发展) Access团队开发).它与Jet并没有太大区别，只是它是数据库引擎的新版本，并且具有Jet所缺少的功能. 可空布尔值不是添加的功能之一.无论如何，如果我没记错的话，关于布尔值是否应该为可空值，而Jet/ACE则认为应该不应该为布尔值，这在理论上有很大争议. 即使在Access/Jet/ACE中，非空布尔值也会引起问题(艾伦·布朗(Allen Browne)已经讨论过这样的问题，左连接).我的建议是将字段更改为可空位，字节或整数字段(我不确定SQL Server中确切的数据类型，也不知道与Access/Jet/ACE最兼容的数据类型).
或者，您可以通过使用将CAST()服务器端布尔值转换为INT的视图来解决BIGINT问题.这使其不可编辑，但是(与BIGINT一样)，您可以将原始字段保留在VIEW中，并使用适当的值对其进行写入，而CAST()版本仅用于显示. 对于价格而言，Access的SSMA将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!). 这篇关于MS Access链接表中的可空布尔字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
