VBA Code to Loop through Table of SQL Queries
Posted on 2013-11-04
Bear with me as I may have trouble describing my dilemma.
Each month, I have to update a table with new activity from most recent month (table name 2013Act). We have put together a series of 40 (+/-) SQL queries that we use to modify and update this one table.
This table then becomes our source for various reporting, etc.
I am capable of writing SQL on my own for simple queries, but have been tasked with automating the process of running the 40(+/-) queries into a single process, using VBA.
We have a total of three tables I am to be using.
Table 1 is zNotes:
Contains fields [Sequence], and [SQL] (Each record is a different SQL query)
[Sequence] field is numbered 1-40, basically putting the [SQL] code into particular order that we run them in.
[SQL] field is the actual SQL code we run to update our source table.
Table 2 is tGlobal:
Contains field [Seq]
This table is going to be used to track the progress of my LOOP.
After each SQL code is ran, I need to update the [Seq] field in tGlobal to its [Sequence] number, from zNotes.
My mentor told me this will allow us to track where we are in process if the code bugs out or if some error arises. We could then easily just start process over where it left off and it would continue running through the remaining [Sequences]/[SQL] codes.
Table 3 is 2013Act:
This is table all my SQL code will select,update,modify,etc.
Apologies for any confusion caused, I had a bit of trouble trying to describe my problem.
Thanks for any and all help,