Update MS Access table and avoid lock
Posted on 2007-10-12
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.