Tony
asked on
Write Conflict
Hi,
I am receiving a Write Conflict on my data entry in Ms-Access.
I am using Ms-Access as a front end, Mysql as a back end.
This Write Conflic msg appears even when one single user is using the application.
Please see the attached picture.
Please advise solution.
Thank you.
Write-Conflict.png
I am receiving a Write Conflict on my data entry in Ms-Access.
I am using Ms-Access as a front end, Mysql as a back end.
This Write Conflic msg appears even when one single user is using the application.
Please see the attached picture.
Please advise solution.
Thank you.
Write-Conflict.png
strivoli
There's something accessing the record at the same time. If there's only one User running probably there's a TRIGGER running.
Check your code. You could be conflicting with yourself. Do you have code that is running an update query or perhaps opening a DAO or ADO recordset and editing records that might include the record on the form?
It is generally poor practice to do any of those things but you can get away with it if you first save the current record.
[code]If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
'''' other code that does bulk update
[/code]
I had same issue when working with Access/SQL Server.
Solution was adding a time stamp column to each linked table.
Solution was adding a time stamp column to each linked table.
Hi,
bfuchs is right, the problem comes with data types which are not very exact, like "float".
Problem is, Access always converts the data of the external database into it's own datatypes. In case of imprecise data types sometimes the value Access sees is different from that on the server and so it thinks that someone else made a change to the record which is the reason for this error.
To find out if someone else has changed a record, each field in the Access form (the old value) will be compared with the value in the backend and if there is a difference then it's a change for Access.
To avoid that with SQL Server you can simply add a timestamp column which is not really a date/time value but a rowversion value which is the new name for this datatype in SQL Server. This is a unique counter over ALL tables which implemented this datatype, so if anything changes the saved row becomes the next counter value.
The good thing is that in case of SQL Server all other values of all other fields are ignored then and Access automatically only uses this timestamp/rowversion column to compare which is a binary value. That avoids conversion problems and you are done.
Unfortunately (at least as far as I know) there is no pendant in MySQL so you can't implement the same here.
I'm afraid you need to change the datatypes in MySQL for all imprecise data types to one which is precise. For example, you used a floating point value in your screen shot. If that is datatype "float" or "double" in MySQL, change that to "decimal(15,2)" (for example) which is an always exact value where Access should have no problem with as it supports the same data type.
More information about datatypes:
https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html
I can not guarantee that this alone will solve your problem, maybe you also need to check more datatypes, I work only rarely with MySQL. As you can also use SQL Server in a free version it may be a better idea to change the backend, if possible.
Cheers,
Christian
bfuchs is right, the problem comes with data types which are not very exact, like "float".
Problem is, Access always converts the data of the external database into it's own datatypes. In case of imprecise data types sometimes the value Access sees is different from that on the server and so it thinks that someone else made a change to the record which is the reason for this error.
To find out if someone else has changed a record, each field in the Access form (the old value) will be compared with the value in the backend and if there is a difference then it's a change for Access.
To avoid that with SQL Server you can simply add a timestamp column which is not really a date/time value but a rowversion value which is the new name for this datatype in SQL Server. This is a unique counter over ALL tables which implemented this datatype, so if anything changes the saved row becomes the next counter value.
The good thing is that in case of SQL Server all other values of all other fields are ignored then and Access automatically only uses this timestamp/rowversion column to compare which is a binary value. That avoids conversion problems and you are done.
Unfortunately (at least as far as I know) there is no pendant in MySQL so you can't implement the same here.
I'm afraid you need to change the datatypes in MySQL for all imprecise data types to one which is precise. For example, you used a floating point value in your screen shot. If that is datatype "float" or "double" in MySQL, change that to "decimal(15,2)" (for example) which is an always exact value where Access should have no problem with as it supports the same data type.
More information about datatypes:
https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html
I can not guarantee that this alone will solve your problem, maybe you also need to check more datatypes, I work only rarely with MySQL. As you can also use SQL Server in a free version it may be a better idea to change the backend, if possible.
Cheers,
Christian
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.