Running SQL Queries 1

[Russie]

Hi.

I have 10 standard SQL Queries to run for 10 regions and I would like to pass the regional variable to the SQL string to do this.

What is the best way to run 10 SQL Queries, one after another, in code?

I've been using...

Set conDatabase = CurrentProject.Connection

strSQL = " [ SQL Query ] "

conDatabase.Execute strSQL

Is this the best way to run 10 queries, with the above repeated 10 times with a different SQL string for each??..

..or is there a better way?

A star for any post that improves what I am doing.

Thanks for anything.

Russie.

 

[mpastore]

Russie,

This thread is quite similiar to one you posted earlier in the month. Is sleep deprivation taking its toll again?

Mike Pastore

Hats off to (Roy) Harper

 

[Russie]

Mike.

I've moved on since earlier and now have my 10 sets of 10 queries being called by one sub (instead of 10 seperate subs) which uses the regional identifier to differentiate which query is to be run.

Now I'm onto running SQL Strings instead of queries, with the regional identifier embedded within the SQL strings. The result of this is for me to have a set of 10 generic SQL Strings instead of my current 100 queries.

So. Given that progress has been made, does anyone know of a 'best practice' for running 10 sets of SQL Strings one after another as opposed to the way detained in the header post of this thread?

Thanks for your reply mpastor, although it isn't quite what I am looking for.

Russie

 

[mpastore]

Is the SQL always the same except for the regional variable? If so you can set up a skeleton SQL string and replace it in a loop.

For example:


Sub sql_loop()

Dim sSQL_skel As String
sSQL_skel = &quot;select * from mytable where myregion='<<REGION>>'&quot;

ReDim aRegions(2) As String ' or open a table that has regions
aRegions(1) = &quot;West&quot;
aRegions(2) = &quot;East&quot;

Dim iCntr As Integer
Dim sSQL_temp As String
For iCntr = 1 To UBound(aRegions)
sSQL_temp = Replace(sSQL_skel, &quot;<<REGION>>&quot;, aRegions(iCntr))
Debug.Print sSQL_temp
'my ADO/DAO runquery code goes here
Next iCntr



Mike Pastore

Hats off to (Roy) Harper

 

[Russie]

Thanks for that Mike.

You have answered my question with your code.

The SQL Strings are all different which necessitates having a seperate pre-defined SQL string for each stage of the reconciliation.

Where you have helped is the Replace() method that you have used to to place the regional identifier into the SQL String, and the use of an array to define the regions.

I appreciate your help.

Russie.