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