Nullable bool fields in MS Access linked tables

Question

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!

Highly

This is a known issue since Access 97: support.microsoft.com/kb/278696/EN-US. Since nothing has changed in the past 14 years, I wouldn't expect any fixes soon... – Donelladonelle

Answer 1

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!).

Vitovitoria

Nullable Bit is fine I think. – Florettaflorette

Hi, sorry for the late response. I checked our SQL dbase, and it is already a nullable bit. There is no other field type that will allow for boolean type data to be stored in it, unless I go for a nullable int and put a constraint of 0/1 on it. It's an option, but has repercussions elsewhere. Not my first choice. – Highly

Re the comment on SSMA upsizing bools to nullable bits - could you elaborate on that a bit? Are you referring to an Access table with bool fields in it being interpreted as a nullable bit when connecting to thios table? I'm going in a different direction - my table is in SQL Server, and I'm using Access to connect to it ... – Highly

When you use the SSMA to upsize an Acesss table with a Boolean field, it gets created in SQL Server as a nullable bit field. In reality, to map 100% correctly, it should be a non-nullable bit field, since the Jet/ACE Boolean field is not nullable, so it's not a 1:1 translation. But the point I was making by citing this is that MS thinks it's the closest match when upsizing, so should be the most compatible data type for an Access front end to SQL Server. – Vitovitoria

"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" - I think you're thinking of "JET Blue" (aka ESE) which is indeed a key part of Windows (e.g. Active Directory uses it for its database) and Exchange Server too, but this is not the same engine as "JET Red" (aka Access) from which ACE is derived from. – Lonergan

Answer 2

Following the analysis in this Microsoft KB http://support.microsoft.com/kb/318882 here is what we did to solve this problem. 1) We ran a sql script to update the rows in that table which had nulls in the bit fields, 2) We then modified the table definition to include a default value of '0' in those bit fields.

Dealate

That's an interesting article. I have had no such problems with the last database I upsized (running A2003 with SQL Server Express 2008 R2), and I did not supply a default value for the Boolean fields. I'll need to do a check, but if the cited problem had occurred, I'd have been getting major error reports for the last 6 months (which I haven't), so it seems like there must be something else going on. Perhaps something was changed between SQL 2005 and 2008. I do wonder if your tables all have a PK and a timestamp field? – Vitovitoria

Answer 3

I experienced the same problem in my project, where I implement some add-ons in our old ordering system from early 2000's, and boolean is widely used.

Today I found a solution by using a passthrough query as follows:

SELECT 
    *,  
    CASE MYFIELD 
       WHEN NULL THEN NULL 
       WHEN 1 THEN 1 
       WHEN 0 THEN 0 
    END AS MYRESULT
FROM 
    DBO.MYTABLE

This is not updateable but anyway regular SQL statements work there.

Radiophone