Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-01-2018, 03:05 PM   #1
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 346
Thanks: 76
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
SQL linked table record update

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.

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard

Last edited by mjdemaris; 07-01-2018 at 03:12 PM. Reason: additional info
mjdemaris is offline   Reply With Quote
Old 07-01-2018, 03:22 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: SQL linked table record update

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
mjdemaris (07-02-2018)
Old 07-02-2018, 08:23 AM   #3
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 346
Thanks: 76
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: SQL linked table record update

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.

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-02-2018, 09:48 AM   #4
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 346
Thanks: 76
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: SQL linked table record update

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
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-02-2018, 10:01 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: SQL linked table record update

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-02-2018, 03:34 PM   #6
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 346
Thanks: 76
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: SQL linked table record update

Always feels good to solve a problem like that!
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-03-2018, 12:24 AM   #7
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,284
Thanks: 151
Thanked 1,683 Times in 1,655 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: SQL linked table record update

I've always been puzzled by this behaviour, as Access does allow a "tri-state" checkbox, which does "set"/allow a null value.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 07-03-2018, 02:32 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: SQL linked table record update

Quote:
Originally Posted by Minty View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-03-2018, 08:36 AM   #9
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 346
Thanks: 76
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: SQL linked table record update

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

__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Reply

Tags
sql 2016 , update , write conflict

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Insert/update values into linked table (linked from sql server via odbc connection) dejanc Tables 2 12-17-2015 04:31 AM
Linked Outlook table and update query to another table wotme Queries 0 09-23-2012 03:23 PM
Picking specific fields from a linked table to update a local table jonathanchye General 5 05-05-2011 04:28 AM
ODBC - update on a linked table <table name> failed anandsbr Forms 10 12-01-2010 07:54 AM
[SOLVED] Using and Append (or update) Query to update a table from a linked table. Plim Queries 3 10-17-2001 08:25 PM




All times are GMT -8. The time now is 06:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World