• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 837
  • Last Modified:

Update MS Access table and avoid lock

I have a form that displays aggregate client numbers from a table A. This table A is generated from a Make-Table Query. I find for my particular application, it is easier and much faster to query many client numbers off this table A instead of running individual client queries against the production database.

I wanted to give my users a way to refresh the count on the form for the particular client they are viewing. So, I setup a button that runs the Make-Table Query and in the code I have the form object requery to display the up-to-date numbers.

PROBLEM = I get an error message indicating 'the database engine could not lock the table <table name> because it is already in use by another person or process.

I understand that the underlying table being updated (i.e. Table A) is also the table I am trying to generate when I run my query again to refresh. How can I resolve this through some clever design that allows me to get around this issue.

  • 2
1 Solution
Try doing a recalc on the  control that displays the count instead of requerying the entire form.
htamraz1Author Commented:
But the recalc has to first tap into an updated pool of data (i.e Table A). The problem is re-generating Table A as I explained earlier. If you meant something else...let me know.
then try using this as the control source of the textbox displaying the count:
= DCount("[YourField]", "tableA", "[ClientID] = " & Me!ClientID)

this should not require a table lock
Hi htamraz1,

Instead of running a Make-Table Query  try doing delete & Append

Good Luck!


Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now