MS Access, MySQL ODBC Write Conflict Error

On one of my projects we have a MS Access database front end
hooked up to tables on a MySQL backend.

The entry process seemed to be going just fine until my customer
encountered the “Write Conflict Error”.

This error was frustrating to try to track down and fix.
While I was trying to diagnose the error, I realized that new entries were not
affected by the “Write Conflict”, only existing records.

I “googled” the issue, and tried to piece together a solution, and
I finally did.

First of all

I opened the ODBC settings of the MySQL link.

Click on the “Details” dropdown.

Select the following checkboxes (where provided):

-Return matched rows instead of affected rows (Connection or Cursors/Results tab)
-Allow big result sets (Connection tab)

These instructions are from http://www.sparxsystems.com/enterprise_architect_user_guide/10/projects_and_teams/setupmysqlodbcdriver.html


Then I went into the design view (structure) of the MySQL table and set the TIMESTAMP field’s attribute to “ON UPDATE CURRENT_TIMESTAMP”

The reasoning behind this (according to me) is that when you are adding a new record, you receive a timestamp of the current date and time. However, if you modify the record from the table. That “date_entered” timestamp field is never updated again, so really you would never know that record was updated. By adding the “ON UPDATE CURRENT_TIMESTAMP” attribute,you are telling that database row that it was updated at a specific time, which is different from anyone else who may be entering into that table.

Relink the tables in Access using the “Linked table manager” and the “Write Conflict” issue was solved.

Hope it helps you.

Tags: , , , ,
Previous Post

Next Wave of Tech Disruptions

Next Post

Video – Jack Bogle – Vanguard Fund Founder