Solved

VBA Code to Loop through Table of SQL Queries

Posted on 2013-11-04
9
3,528 Views
1 Endorsement
Last Modified: 2013-11-04
Morning all,

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,
Gary
1
Comment
Question by:syntekz187
  • 4
  • 3
  • 2
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39621502
first, you have to open the table as recordset and formulate the SQL statement based on the fields returned by the recordset..

dim rs as dao.recordset, sSQL as string, j as integer
set rs=currentdb.openrecordset("zNotes")
 sSQL = "Select "
for j=0 to rs.fields.count-1
     sSQL = ???

next

but, without seeing the contents of the tables, it will be difficult to compose the SQL statement.
0
 

Author Comment

by:syntekz187
ID: 39621542
In our zNotes table we have two fields, [SQL] and [Seq]
[Seq] contains a numbered value, basically puts the SQL code into an order that they should be run (1 thru 40).
The [SQL] field contains the actual SQL query code, example below:

UPDATE 2013Act SET [2013Act].HmdaActn = ""1"", [2013Act].StateActn = ""6""
WHERE ((([2013Act].HmdaQual)=""Hmda"") AND (([2013Act].Channel)=""Wls"") AND (([2013Act].LkwActn)=""6""));

Open in new window


The tGlobal table just contains field [Sequence] and is to be used to track progress, should be updated after each SQL code is ran with its corresponding [Seq] value.

2013Act table is table all of my queries will be updating.



I've written VBA code for similar projects where I ran a dozen separate SQL queries in one project. I had all of my SQL code in the VBA project, written out.

My task now is to instead store all of the SQL code on the zNotes table, and use a Loop to run each SQL code. The code itself sits on the zNotes table though, in [SQL] field.

I hope I am making some sort of sense, is this possible with a loop?

Thanks again!

P.S. In my mind, only fields I should be hitting with my code are the zNotes.[SQL], zNotes.[Seq], and tGlobal.[Sequence] fields.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 39621677
Give this a try:


Dim rs as dao.recordset
set rs = currentDB.OpenRecordset('SELECT Seq, [SQL] AS ActionQuery FROM zNotes ORDER BY Seq", dbOpenDynaset)

if rs.recordcount = 0 then
    msgbox "No records found in zNotes"
    exit sub
end if

Do Until rs.EOF
      ' run the action query
      CurrentDB.execute rs!ActionQuery, dbfailOnError
      ' Update the log table:
      CurrentDB.Execute "UPDATE tGlobal SET Sequence = " & rs!Seq
       rs.movenext
loop

Open in new window

0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 333 total points
ID: 39621710
try this


dim rs as dao.recordset, ssql
ssql="SELECT Seq, [SQL] AS Q FROM zNotes ORDER BY Seq"
set rs = currentDB.OpenRecordset(ssql)

if rs.eof then
      exit sub
      else
      rs.movefirst
end if

Do Until rs.EOF
      ' run the  query
      CurrentDB.execute rs!Q, dbfailOnError
      ' update the log table
      CurrentDB.Execute "UPDATE tGlobal SET Sequence = " & rs!Seq
       rs.movenext
loop
               

if Seq is text datataype change this              


 CurrentDB.Execute "UPDATE tGlobal SET Sequence = " & rs!Seq

with

 CurrentDB.Execute "UPDATE tGlobal SET Sequence = '" & rs!Seq & "'"
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:syntekz187
ID: 39622218
Thank you both for your timely responses and assistance. I have been pulled away from this project this afternoon so haven't been able to implement and test it just yet.

From looks of it though; this is exactly what I was looking for. At least the general structure is here and that is what I needed.

Thanks again! I will choose the Solution tomorrow once I can test this in action.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39622486
Small typo in my post -

You'll probably get it independently, but just in case...

I started the SQL string on line 2 out with a single quote, which will treat the rest of the line as a comment.  That should have been a double quote.

So line 2 should look like this:

set rs = currentDB.OpenRecordset("SELECT Seq, [SQL] AS ActionQuery FROM zNotes ORDER BY Seq", dbOpenDynaset)

Open in new window



With that replacement:

Dim rs as dao.recordset
set rs = currentDB.OpenRecordset("SELECT Seq, [SQL] AS ActionQuery FROM zNotes ORDER BY Seq", dbOpenDynaset)

if rs.recordcount = 0 then
    msgbox "No records found in zNotes"
    exit sub
end if

Do Until rs.EOF
      ' run the action query
      CurrentDB.execute rs!ActionQuery, dbfailOnError
      ' Update the log table:
      CurrentDB.Execute "UPDATE tGlobal SET Sequence = " & rs!Seq
       rs.movenext
loop

Open in new window

0
 

Author Comment

by:syntekz187
ID: 39622566
Got the code put together, made a couple adjustments, but running into one problem that I don't know how to handle in my code.

Here is what my code looks like now:
Private Sub Command0_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

sSQL = "SELECT Sequence, [SQL] AS ActiveQuery FROM zNotes ORDER BY Sequence"

Set rs = db.OpenRecordset(sSQL)

If rs.EOF Then
    MsgBox "No Records Found, please verify data exists."
    Exit Sub
    Else
    rs.MoveFirst
End If

Do Until rs.EOF
'Run Queries here
    db.Execute rs!ActiveQuery, dbFailOnError
'Update the tGlobal table
    db.Execute "UPDATE tGlobal SET [Seq] = " & rs![Sequence]
    rs.MoveNext

Loop


Set db = Nothing
Set rs = Nothing

End Sub

Open in new window


The loop is hanging up on line 22 above: db.Execute rs!ActiveQuery, dbFailOnError

Run-time error '94': Invalid use of Null

This is happening because on my zNotes table, there are a few records that either have NULL value for [Seq] or NULL value for [SQL].

Can someone help me add an additional statement to handle that type of occurrence? I would like the code to see NULL value (for either [Seq] or [SQL] on the zNotes table) and ignore that record and just move to the next record and process it.

Thanks again and have a great evening!
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 333 total points
ID: 39622587
you could either change this

sSQL = "SELECT Sequence, [SQL] AS ActiveQuery FROM zNotes ORDER BY Sequence"

with

sSQL = "SELECT Sequence, [SQL] AS ActiveQuery FROM zNotes Where [SQL] is not Null And Sequence Is Not Null ORDER BY Sequence"

or change the codes

Do Until rs.EOF
'Run Queries here
    if rs!ActiveQuery & ""="" or rs![Sequence] & ""="" then
        'do nothing
     else

    db.Execute rs!ActiveQuery, dbFailOnError
'Update the tGlobal table
    db.Execute "UPDATE tGlobal SET [Seq] = " & rs![Sequence]


     end if
    rs.MoveNext

Loop
0
 

Author Closing Comment

by:syntekz187
ID: 39622609
New to this site, but great first time experience. Didn't realize there was a community like this that was so active and professional.

Appreciate both capricorn1 and mbizup's assistance, you really made my job easier.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 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

15 Experts available now in Live!

Get 1:1 Help Now