Solved

VBA Code to Loop through Table of SQL Queries

Posted on 2013-11-04
9
3,545 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

910 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

16 Experts available now in Live!

Get 1:1 Help Now