Fix Microsoft Access Delete Query Locking Conflict on a Linked MySQL Table
Question details
Resolve a locking conflict when Microsoft Access deletes a single record from a linked MySQL table.

- Product
- Microsoft Access and MySQL
- Device & OS
- not provided
- Scenario
- A user runs a standard Access DELETE query against a linked MySQL table.
- Observed behavior
- Access reports a locking conflict for a single-record deletion, although group deletions, pass-through queries, or selecting and then deleting may work.
Identify the linked MySQL table and check its primary key and LONGTEXT columns. Ask a database administrator to review the maximum stored text length, schema dependencies, and permissions before changing a column type.
Change the affected MySQL LONGTEXT column to VARCHAR
Use this approach when a LONGTEXT column is contributing to the single-record delete conflict.
In the reported case, changing the affected column from LONGTEXT to VARCHAR allowed Access to delete records. This may not resolve every locking conflict.
The VARCHAR length must accommodate all existing values and comply with MySQL row-size and character-set limits. Preserve the column's nullability, default, and other required attributes when changing its definition.
In your MySQL administration tool, run SHOW CREATE TABLE for the affected table and identify its LONGTEXT columns.
Run a SELECT query using MAX(CHAR_LENGTH(column_name)) on each candidate column to determine the longest stored value. Check application requirements and MySQL limits before choosing a VARCHAR length.
Have an authorized administrator use ALTER TABLE in the MySQL administration tool to change the affected column to an appropriately sized VARCHAR, preserving its required attributes.
In Access, open Linked Table Manager, refresh or relink the table, and run the standard DELETE query against a suitable test record.

Verify the primary key and refresh the linked table
Use this when Access may not have current key or table metadata for the MySQL link.
Compact and repair the Access database
Try this if the conflict persists after checking the MySQL table and refreshing the link.
Work with Office files in WPS Office
WPS Office is a free, lightweight alternative for everyday documents, spreadsheets, and presentations. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files. It does not fix Access-to-MySQL locking conflicts or replace the linked Access database.

Frequently Asked Questions
Why can a MySQL LONGTEXT column cause an Access delete conflict?
In the reported case, a LONGTEXT column was associated with the locking violation, and changing it to VARCHAR resolved the issue. The exact cause can depend on the Access version, driver, and table schema, so verify the change with a test.
Does the linked MySQL table need a primary key?
A primary key helps Access identify individual rows reliably. Check the MySQL table with SHOW CREATE TABLE, then refresh the link in Access using External Data > Linked Table Manager.
What if a pass-through DELETE works but the Access DELETE query fails?
A pass-through query sends SQL directly to MySQL, while a standard Access query uses the linked-table connection. Check LONGTEXT columns and the primary key, refresh the link, and test the standard query again.
Will Compact and Repair change the MySQL table?
No. Database Tools > Compact and Repair Database operates on the Access database, not the linked MySQL table. Make MySQL schema changes through an authorized MySQL administration tool.




