SQL linked table record update (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 07:37
Joined
Jul 9, 2015
Messages
424
So, Access is linked to an SQL Express Database. I have an update issue.
Currently, the problem is not related to forms/queries/vba.
I can change data in one table using SSMS, but when I try to change the data in Access, opening the table directly, I cannot change anything, except for the first three records - that worked, but now I cannot.
I did have a default constraint for a time stamp field, but I removed the constraint, no change. So, I deleted the field, no change.

I've been down a road like this before, but I think the way I fixed it was to delete the table and rebuild it, but I have a lot of data here that I don't want to have to rebuild...unless that's the only option.

Forgot to add that the error message is: Write Conflict. This record has been changed by another user since you started editing it....

thanks.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 14:37
Joined
Jan 14, 2017
Messages
18,186
There are several reasons. Here are two of the most likely reasons for your SQL table(s):

a) It does not contain a primary key - this makes it read only but shouldn't cause a write conflict
SOLUTION: Add a PK field to ALL tables

b) It contains boolean (yes/no) fields with no default value. In Access such fields can only have 2 values true/false. However in SQL Server, null is also possible. If you try to update records where one or more boolean fields has a null value, Access throws a 'wobbly' as it doesn't know what to do - causing the write conflict
SOLUTION: Go through each table, update all null boolean values to true or false & set a default value for each

Relink all tables after checking the above

HTH
 

mjdemaris

Working on it...
Local time
Today, 07:37
Joined
Jul 9, 2015
Messages
424
So, even though the boolean field already has a value, in this case -1 (bit), it doesn't like to take the update to any other field?
Ah, there are two bit fields, no defaults set for either...
PK is not an issue here, I made sure to set those! (bad experience with that already, lol)
I'll let you know if this works.
Thanks.
 

mjdemaris

Working on it...
Local time
Today, 07:37
Joined
Jul 9, 2015
Messages
424
That seemed to work, although Access showed a value of 0 for all records, i updated all records anyway to 0, and the other field to -1, just to be sure, and it worked.
Now, to add the time field back in.

And...that works! Thanks bud
 

isladogs

MVP / VIP
Local time
Today, 14:37
Joined
Jan 14, 2017
Messages
18,186
No problems.
It took me several months to pin down the cause to SQL boolean fields.
The fact that Access shows those with null values as false doesn't help

IIRC, the error only occurred when I tried to run action queries on those tables

Since I fixed that about 5 years ago, I've NEVEr had a write conflict error
 

mjdemaris

Working on it...
Local time
Today, 07:37
Joined
Jul 9, 2015
Messages
424
Always feels good to solve a problem like that!
 

Minty

AWF VIP
Local time
Today, 14:37
Joined
Jul 26, 2013
Messages
10,354
I've always been puzzled by this behaviour, as Access does allow a "tri-state" checkbox, which does "set"/allow a null value.
 

isladogs

MVP / VIP
Local time
Today, 14:37
Joined
Jan 14, 2017
Messages
18,186
I've always been puzzled by this behaviour, as Access does allow a "tri-state" checkbox, which does "set"/allow a null value.

Hi Minty
That raises some interesting points....

The tristate checkbox is allowed in forms but the tristate is not allowed in boolean table fields

If a tristate checkbox is unbound you can toggle through true/false/null

However if you use a bound tristate checkbox in a form, it becomes 'partly read only'
That's of course nonsensical but what I mean is this ...
If a bound tristate checkbox is true it can be edited and made false as that is unambiguous
However if its unticked, it can't be made true as that is an ambiguous state (null?/false?)

I assume this is to prevent write conflict errors
 

mjdemaris

Working on it...
Local time
Today, 07:37
Joined
Jul 9, 2015
Messages
424
I've always wondered about that tristate effect on the form controls.
The table shows a value of zero, which should be false, but using a bound control, unless the value is explicitly set to zero, it shows as a solid fill black box, which I take to mean "null".
 

Users who are viewing this thread

Top Bottom