Updating MySQL table through Access creates Locking violations Error

[Craig Gall]

Hello all,

Whether this is the correct forumn i am unsure.

Currently we have a large MySQL database with a coldfusion front end operating within the building.

This database and front end operate fine and there are several bolt on applications that we have over time developed and added to the front end. Mainly import, export and data checking screens.

Recently we had a requirement to build a table to store audit details. The second part of this request was that the audit records would be entered and stored on an Access database and that the Access tables append details to 2 tables (Points and Audits) MySQL database and update 2 fields in a 3rd table (Outlet).

All the processing has been set up to do all the updates and appends from access. So all the passed audits will enter points records in the MySQL points table. All audit records are appended to the Audits table. All this works fine.

However when i attempt to update the values in 2 fields of the MySQL outlet table. Not all records will update due to access reporting a record lock violation. The queries will only update values that need to be updated so not every record in the MySQL outlet table need to be updated. Only a handfull in 1 field or the other.

I would appreciate a response from anyone who may either know why this happens in access or if there is a work around. My aim is to have the process as automated as possible as it is only going to be myself that will have the ability to run this update to the system. I am not an expert in MySQL and my knowledge is very beginner level. The skills within my team are that i am the main access developer and there is a developer for MySQL We each know only our areas of database.

Many thanks for reading and any options.