Access form Sql linked table write conflict

Question

I have spent two days researching and try to fix my issue with access form edits. I understand that there may be similar questions out there, but none of the suggestions fixed my problem. Also, my situation might be slightly different.

I'm on Access 2017 and using an access split form that is tied to a linked table that is on sql server 2017. I have an add button that simply adds the record entered and moves to a new record. When I add a record to my form and then try to edit it in the datasheet view on my split form I get a write conflict error.

I've already validated that I have a primary key on my table and that there are no null bit fields.

The other thing to note is that this started happening after migrating from SQL server 2014 to sql server 2017.

One thing I read about and have yet to try because of the "drastic" change it entails is to set the compatibility level of my database to something lower like SQL 2014. This would be a last resort however and would only be to validate what the cause of the error might be.

I've tried everything on this page that is applicable to my situation: http://www.accessrepairnrecovery.com/blog/fix-ms-access-write-conflict-error

What else can I try to resolve this? I'm hoping someone out there has run into something similar.

Answer 1

First this question has been answered 100's of time on stack overflow.

Next up: Your link has nothing to do with using SQL server, so the suggests likely will not help.

The main causes (repeated over and over as solution) when using Access and SQL server are:

Ensure that all tables have a PK defined.

Ensure that any bit fields have a default setup on sql server (usually 0)

Ensure that each table has a timestamp field.

This is important, espeically if you have any floating or "real" data type columns. The Access up-sizing wizard, and the migration tool for Access both by default suggest and will add the timestamp field.

If you missing any of the above 3 issues (that have been repeated over and over for the last 18 years on near every article about using SQL server.

So, you will ensure that you checked above all 3 issues. After any table changes, you will re-link the access client side.

You then need to test/check if you can change edit data using the linked table directly from access (in table view). if you can edit such data directly, then you are back to testing with your form. If the form still causes a write conflict, then suggests in the article you linked to will START to apply, but not until such time you address and ensure all 3 above steps are issues are dealt with.

The time stamp is often required for a sub form, and also when you have real/floating columns. Due to rounding errors in such computer numbers, then the compare between the two records fail. The adding of the timestamp column fixes this issue since access now does not have to do a field by field compare, but will use the timestamp column (not to be confused with a datetime column) to figure out if record has been changed. Thus adopting this feature even reduces the network chatter from client to server and allows access to determine if server record been changed without having to resort to a field by field compare.

Answer 2

I recently encountered the same error and it turned out to be that I had an active sort on the datasheet view. Once I removed the sort, voila, problem solved! (Nothing like shooting myself in the foot.)