Attack of the Three-State Bits
You know, I really like Access 2003 as an administrator's front-end to a SQL database that is principally backing-up a web site. It never made much sense to me to invest the extra time to wrap a bunch stuff up into ASP.NET to do trivial data edit tasks. I still feel that way but I just got burnt by it a bit.
Well, not just a bit: a three-state bit.
What's a three-state bit you might ask? Easy: its any nullable bit-typed column in SQL Server 2000. Such columns can have values of 0, 1 or null. And yes, there's cases where it makes sense to do that, like voting on a change. Until a person votes, the value is null. Once the vote yea or nay is cast, the value changes to match that.
Unless, of course, you're using Access -- which has a funny way looking at three-state bits. Humor me and try this at home (or work, if you are so brave...) Using the SQL Server 2000 database of your choice, run this query set.
create table dbo.AccessBitExample
(
PKID tinyint identity(1,1) primary key not null,
BitField bit
)
go
insert into dbo.AccessBitExample(BitField) values (0)
go
insert into dbo.AccessBitExample(BitField) values (1)
go
insert into dbo.AccessBitExample(BitField) values (null)
go
select * from dbo.AccessBitExample
go
You should get results like this:
Now fire up Access 2003 and using the linked table manager, create an ODBC connection to that database and linked table for dbo.AccessBitExample. Then open that table in Access. You should see something like this:
Interesting. It gets better. In Acess, try to change the 0 in PKID=3 to 0. Yes, I know its already showing zero, but we know its really NULL, right?
Nice. Near as I can figure out, Access is trying to make sure you're not stomping on a changed record by comparing what it thinks was there with was there now before it writes your change. However, Access thinks the original value for that BitField is 0, not Null, so it throws this message since it would trying to change something that was already changed (but wasn't.)
The work-around? Use Query Analyzer or something other than Access 2003 to make those specific changes.
The prevention? Well, that's harder. Personally I'd call this an Access bug because it malforms a null bit to false rather than respecting it nullness. I like bits, they make sense to me. But then some folks would say that I'm getting what deserved for using three-state bits to begin with.
To avoid this problem, consider using TinyInt instead of Bit. TinyInt's nullness does seem to get respected by Access and you can still have null, zero or one as values just as you did before. Of course, you have and addtional 253 potential values too... and that might useful.
Afterall, this behavior might just be a feature, not a bug...