Solved

VBA Code to Loop through Table of SQL Queries

Posted on 2013-11-04
9
3,643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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