Can not update a table from MS Access by not obvious reason.

Had a call this morning: After conversion MS Access based interface generates an error when users try to change data in a table.

The most common reason for that type of errors is not having primary key on the table or any other unique key or even field, such as identity column.

However not in this case. It was a "Write conflict".
The error was: "This record has been changed by another user since you started editing it. if you save the record, you will overwrite the changes the other user made."

Here is the screenshot of that error:

It looks like, every time I open MS Access and going to the particular record, somebody else is looking from my back and do some changes in the same record before I commit my changes.
Isn't it weird?

After doing some research in the Internet I've found that this problem can be caused by BIT column which does not have DEFAULT value and is nullable.

That is the difference from MS Access SQL and T-SQL: In MS Access you can have only two possible values for BIT fied: 0/1 | Yes/No | True/False ..., but in T-SQL could be three conditions: Yes/No/Do not know (0/1/NULL);

Obviously, MS Access can not comprehend that and returns such a weird error.

In order to fix that problem three followings simple actions have to be performed:

1. All NULL values in BIT columns have to be changed to 0 or 1.
2. For all these columns DEFAULT values have to be specified.
3. All these columns have to be altered to be NOT NULLable.

I do not very like cursors, loops and automated executions. So, I've created a simple script to generate "Fixing" script:

DECLARE @Default CHAR(1); SET @Default = '0'; SELECT 'ALTER TABLE ' + t.name + ' ADD CONSTRAINT DEF_' + t.name + '_' + c.name + ' DEFAULT ' + @Default + ' for ' + c.name + '; ' + 'UPDATE ' + t.name + ' SET ' + c.name + ' = ' + @Default + ' WHERE ' + c.name + ' IS NULL; ' + 'ALTER TABLE ' + t.name + ' ALTER COLUMN ' + c.name + ' BIT NOT NULL; GO' ,* FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.object_id = c.object_id WHERE c.system_type_ID = 104 and c.is_nullable = 1

* Please note that in that case default value as zero is assigned to ALL BIT columns, it might not be your case. However, if somebody did not care about having NULLs, why would they care about zeros? There might be only the special case when these values are used in WHERE clause and NULL is supposed to play a role in that logic.

Here is an example of that script:

ALTER TABLE EmployeeName ADD CONSTRAINT DEF_EmployeeName_ClericalStaff DEFAULT 0 for ClericalStaff; UPDATE EmployeeName SET ClericalStaff = 0 WHERE ClericalStaff IS NULL; ALTER TABLE EmployeeName ALTER COLUMN ClericalStaff BIT NOT NULL; GO ALTER TABLE EmployeeName ADD CONSTRAINT DEF_EmployeeName_GroupInterview DEFAULT 0 for GroupInterview; UPDATE EmployeeName SET GroupInterview = 0 WHERE GroupInterview IS NULL; ALTER TABLE EmployeeName ALTER COLUMN GroupInterview BIT NOT NULL; GO ALTER TABLE ReasonForVisit ADD CONSTRAINT DEF_ReasonForVisit_FI_FS DEFAULT 0 for FI_FS; UPDATE ReasonForVisit SET FI_FS = 0 WHERE FI_FS IS NULL; ALTER TABLE ReasonForVisit ALTER COLUMN FI_FS BIT NOT NULL; GO

As you can see, that script performs all three actions for each column individually.

** You can face couple of problems running this:
1. If you have too many nullable bit columns in huge tables every update will reshuffle your big tables. So, be concerned about I/O.
2. As noted before, you might not need to put ZERO as a default for ALL BIT columns. In this case you can manually edit the script.

*** Biggest Disclosure: Would say these NULL values play some role in some internal queries, which are using "IS NULL" or "!= 1" clauses against changed fields. After your change these queries will return different results.
That means in these cases you can't use proposed approach and have to create VIEWs, which exclude nullable BIT columns, and link these VIEWs in MS Access instead of tables. Unfortunately in that case you won't be able to edit excluded fields.