We help IT Professionals succeed at work.

run access queries in loop

Arkawa72
Arkawa72 asked
on
Medium Priority
267 Views
Last Modified: 2012-05-12
I have a number of queries that I run in sequence. E.g.

strSQL1 = "SELECT * INTO TEST2 FROM TESTIAT;"
strSQL2 = "SELECT * INTO TEST3 FROM TESTIAT;"
.....
strSQL25 = "SELECT * INTO TEST25 FROM TESTIAT;"

I run them one by one using:
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
etc.

I would like to change the DoCmd.RunSQL lines using a loop. But I keep on running into errors. Please help.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try it like this:

For I = 2 to 10 Loop
strSQL = "SELECT * INTO TEST" & I & " FROM TESTIAT"
Currentdb.execute strSQL, dbFailOnError
Next
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
<< For I = 2 to 10 Loop >>

Revise the loop endpoints as needed.  This goes from 2 to 10.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry - my syntax is off...

For I = 2 to 10
      strSQL = "SELECT * INTO TEST" & I & " FROM TESTIAT"
      Currentdb.execute strSQL, dbFailOnError
Next
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
<But I keep on running into errors>
What kind of errors?

You can run into issues with running multiple Action queries sequentially if don't have Iron-clad error handling (Roll-Backs, ...etc) in place if one or more of the queries fails.

...Especially if each subsequent query is dependent on the results of the preceding query...

JeffCoachman

Author

Commented:
What I tried is this:

strSQL1 = "SELECT * INTO TEST1 FROM TESTIAT;"
strSQL2 = "SELECT * INTO TEST2 FROM TESTIAT;"
....

For I = 1 To 25

AAA = "strSQL" & I
CurrentDb.Execute AAA, dbFailOnError
Next

And then I get the error that

Runtime error '3078': The Microsoft Jet database engine cannot find the input table or query 'strSQL1'.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Arkawa72

no points please

The reason you are getting that error is there is no saved query named "strSQL1".  If that was a saved query, that technique would work.  Instead, build the SQL string inside the loop, as indicated by mbizup

For I = 2 to 10
      strSQL = "SELECT * INTO TEST" & I & " FROM TESTIAT"
      Currentdb.execute strSQL, dbFailOnError
Next
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
For a slight performance boost an efficiency improvement ... no reason to keep executing CurrentDB in side the loop, which refreshes all collections.

Dim db As DAO.Database
Set db = CurrentDB


For I = 2 to 10
      strSQL = "SELECT * INTO TEST" & I & " FROM TESTIAT"
      db.execute strSQL, dbFailOnError
Next

mx
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
BTW, why are you creating all of these individual tables that are identical to TESTIAT?

Seems like a really bad plan to me!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
I agree with fyed...

<< E.g. >>

However, I'm looking at these as representative SQL statements versus exact statements that would work in your DB.

If there are sequential variations in the names of the tables you are inserting records into, or in the names of the tables you are selecting data from, then you could build the SQL in a loop per my previous post.

<<I would like to change the DoCmd.RunSQL lines using a loop. >>

However, if you *only* want the query exectution in the loop...  if the names of tables you are selecting from (or any other complexity in  the query) warrants seperate lines of code for each SQL string as displayed in your original post -- ie: if they can't be dynamically built in a loop, you could do something like this with an array (IMO, there is not much gain to this, though):

Dim db As Database
Set db = CurrentDB
Dim I as Integer
Dim strSQL(25) as string

strSQL(1) = "SELECT * INTO TEST2 FROM SomeTable;"
strSQL(2) = "SELECT * INTO TEST3 FROM AnotherTable;"
'.....
strSQL(25) = "SELECT * INTO TEST25 FROM YetAnotherTable;"

For I  = 1 to 25
   db.execute strSQL(I), dbfailonerror
Next

Open in new window


(The Execute statement and RunSQL accomplish the same thing, however my preference is Execute because couple with dbFailOnError, it gives you meaningful error messages if your query fails).

Explore More ContentExplore courses, solutions, and other research materials related to this topic.