Others

Fix Microsoft Access Delete Query Locking Conflict on a Linked MySQL Table

Amos GikundaAmos Gikunda Nov 14, 2026 868 views

Question details

Resolve a locking conflict when Microsoft Access deletes a single record from a linked MySQL table.

Fix an Access Delete Query Locking Conflict on 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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the table

In your MySQL administration tool, run SHOW CREATE TABLE for the affected table and identify its LONGTEXT columns.

2
Check stored lengths

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.

3
Change the column type

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.

4
Refresh and test

In Access, open Linked Table Manager, refresh or relink the table, and run the standard DELETE query against a suitable test record.

Change the affected MySQL LONGTEXT column to VARCHAR
Check the schema change first: Changing a column type can affect stored data and dependent applications. Test the change in an appropriate environment and confirm that no values will be truncated.
Free Microsoft Office alternative

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.

Review and analyze exported table data in spreadsheetsOpen and edit Microsoft Office formats including DOCX, XLSX, and PPTXUse familiar document, spreadsheet, and presentation toolsMove existing Office files into a lightweight productivity suite
microsoft office alternative - wps office

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.