Update MS Access table and avoid lock

Posted on 2007-10-12
Last Modified: 2008-01-09
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.

Question by:htamraz1
    LVL 38

    Expert Comment

    Try doing a recalc on the  control that displays the count instead of requerying the entire form.

    Author Comment

    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.
    LVL 38

    Expert Comment

    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
    LVL 46

    Accepted Solution

    Hi htamraz1,

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

    Good Luck!


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now