Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005

    Unanswered: Queries running too fast that they don't work right?

    I have a simple little script that's controlling several queries that calculate percentages of dollar amounts. This program should work perfectly, but I'm noticing that Access can't seem to keep up..almost like some kind of lag its going through even though it reaches my completed phase. I'm wondering if there's a way to get the Macro/script to only go to the next step once it's 100% completed the step before it?

    -Copy/paste report data into a "loading table" that gets appended to another table once the process gets moving. We'll call this table the "loading table."
    -VB script to create monthly entries for all stores (not all stores appear in our report result, so if we have 5 stores total, maybe only 2 will be in our report. We need to have a record for all entries regardless of if they have sales that month. We'll call this table "monthly results." The calculated data is ultimately inputted into this table via an Update query
    -Calculate the actual percentages of sales done in the "loading table" and update the "monthly results" table. Basically here, we're essentially dumping all of the data from the "loading table" into the "monthly results" table + doing whatever calculations in the query and putting that info into the "monthly results" table as well.

    The problem here is that sometime between steps 2 and 3 it seems to almost be getting ahead of itself. Any suggestions on how to make sure one process completes before another stops? It's all controlled via the script doing simple SQL queries in the VBA code for step 2 and controlling two canned queries I have in Access with the docmd.openquery function. I opted for the canned queries because they were easier to maintain than having to type all of this stuff out in VBA and have the query execute from there, which is why this process seems a little more complicated than it has to be.

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    For aa= 1 to 100000
    Next aa
    This will slow down msaccess for you
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you have a series of scripts then it may be better to consolidate them into one script (I presume you mean module) or function / sub routine

    you can get the same effedct by putting calls to all those scripts in one

    either solution will make sure that each action is processed sequentially. Of the two I'd prefer putting them in a function or sub routine on the grounds that you can provide feedback to the user about what you are doing, and / or write to a log what you are doing somehting which can be a pain and sometimes tricky from a macro

  4. #4
    Join Date
    Feb 2004
    Chicago, IL
    If you are running SQL using OpenQuery I believe it runs asynchronously (doesn't wait to complete the query before executing the next statement). I don't have time to look now but I remember that executing the query with either:

    DoCmd.RunSQL or

    Will execute it synchronously. I believe it is the Execute.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts