How to Fix Microsoft Access Table Lock Errors in Make-Table Queries
Question details
Microsoft Access is unable to lock a table during a Make-Table query because a subform is actively using it as its record source.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user attempts to execute a Make-Table query to add new bank transactions, but the target table is already bound to an open subform.
- Observed behavior
- Access throws a table lock error stating the table is already in use, preventing the Make-Table query from recreating or modifying the table.
Identify the exact name of the subform control and the Make-Table query causing the conflict before modifying your database's VBA code.
Temporarily Unbind the Subform Record Source via VBA
Prevent the table lock error by temporarily removing the table as the subform's record source while the query executes.
Since the subform continuously reads from the table, locking it prevents any structural changes. You can use Visual Basic for Applications (VBA) to drop the connection briefly, run your query, and re-establish the connection.
Open your main form in Design View, select the button or event that triggers the query, and open the VBA Editor.
Add code to set the subform's record source to nothing. For example: Me.YourSubformControl.Form.RecordSource = ""
Run your Make-Table query using the command: CurrentDb.Execute "YourQueryName", dbFailOnError
Reassign the original table name or SQL statement back to the subform's record source, then refresh the data by calling Me.YourSubformControl.Requery
Use Delete and Append Queries Instead of Make-Table
Replace the Make-Table query with Delete and Append queries to avoid recreating the table structure, which frequently causes locking conflicts.
Optimize Repeated VBA Insert Blocks with a Loop
If your process involves multiple similar INSERT blocks to build a running total, use a VBA loop to streamline the code and reduce execution errors.
Looking for a Reliable Microsoft Office Alternative?
While complex database management requires specialized software, your everyday productivity tasks like spreadsheets, documents, and presentations can be efficiently managed with WPS Office. Enjoy a lightweight, highly compatible, and free alternative to Microsoft Office.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
- 2. Install the Software: Run the downloaded installer and follow the on-screen prompts for a fast, lightweight setup.
- 3. Open Your Office Files: Instantly open, edit, and save your existing Word, Excel, and PowerPoint documents with full format compatibility.
Frequently Asked Questions
Why does Microsoft Access report that a table is already in use?
This error occurs when a query attempts to obtain an exclusive lock on a table to make structural changes (such as dropping and recreating it during a Make-Table query) while another database object, like an open form or subform, is currently reading data from it.
Is a Make-Table query better than an Append query?
Generally, no. Make-Table queries recreate the table structure every time they run, which can lead to database bloat and frequent locking issues. Using a permanent table combined with Delete and Append queries is much more stable for regular updates.
How do I clear a subform's record source in VBA?
You can clear the record source by setting it to an empty string in your VBA code using a line like Me.SubformName.Form.RecordSource = "" before running your query. You can then reassign the table name or SQL string when the query finishes.
