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.
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 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
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
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:
Will execute it synchronously. I believe it is the Execute.