Tuesday, September 2, 2014

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:

* 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:

ALTERTABLE EmployeeName

ADDCONSTRAINT
DEF_EmployeeName_ClericalStaff

DEFAULT 0
for ClericalStaff;

UPDATE EmployeeName

SET ClericalStaff = 0

WHERE ClericalStaff ISNULL;

ALTERTABLE EmployeeName

ALTERCOLUMN
ClericalStaff BITNOTNULL;

GO

ALTERTABLE EmployeeName

ADDCONSTRAINT
DEF_EmployeeName_GroupInterview

DEFAULT 0 for
GroupInterview;

UPDATE EmployeeName

SET GroupInterview = 0

WHERE GroupInterview ISNULL;

ALTERTABLE EmployeeName

ALTERCOLUMN
GroupInterview BITNOTNULL;

GO

ALTERTABLE ReasonForVisit

ADDCONSTRAINT
DEF_ReasonForVisit_FI_FS

DEFAULT 0 for
FI_FS;

UPDATE ReasonForVisit

SET FI_FS =
0

WHERE FI_FS ISNULL;

ALTERTABLE ReasonForVisit

ALTERCOLUMN
FI_FS BITNOTNULL;

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.