Batch processing SQL query strings in VBA

[finnbarr99]

What Im trying to achieve is to pull a series of subsets of data from an sql dbase and dump them into a sheet in excel, do some statistical calculations and store those values.

I found a procedure to pull recordsets as follows;

Sub Get_Data_With_SQL()

Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:\CustDbase.accdb"

MySQL = "SELECT ..........." & _ '''''''''select some fields here
" FROM .................." & _ '''''''''from some tables here
" WHERE ((((Table1.ID)=1) AND ((Table2.ID)=15)) AND ((Table3.ID)=12)" & _ '''''''''where the records meet specific criteria
" ORDER BY........." '''''''''and set order by highest to lowest or whatever

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'''''' and then some more code that dumps the recordset into an excel sheet and does the statistical analysis etc.

End Sub

This works fine if there is just one subset of data to extract, however, I need to extract over a thousand different subsets of data so I dont want to hard code each set of values into the WHERE clause in distinct sub procedures, I want one procedure that loops through an array/table and extracts each subset for me.

As an example of the array/table I want to use see below

Table1_ID Table2_ID Table3_ID
1 15 12
1 28 12
1 94 6
3 15 6
3 34 50
3 94 24
3 98 7
7 12 6
7 19 32

and so on, there are over 1,000 rows.

Ive created a table listing all the required combinations from a query I constructed in Access.

I want to rewrite the above procedure to loop through the array/table and pick up the values from the first row (1, 15, 12) and insert those values into the SQL query string then, once the rest of the code has executed, loop to the next row of the array/table and insert those values into the SQL string, and so on.

Just in case im not being entirely clear, using the values from the array/table above, the WHERE clause would read as follows

" WHERE ((((Table1.ID)=1) AND ((Table2.ID)=15)) AND ((Table3.ID)=12)" & _

Then on the next loop it would read

" WHERE ((((Table1.ID)=1) AND ((Table2.ID)=28)) AND ((Table3.ID)=12)" & _

Then on the next loop it would read

" WHERE ((((Table1.ID)=1) AND ((Table2.ID)=94)) AND ((Table3.ID)=6)" & _

Then on the next loop it would read

" WHERE ((((Table1.ID)=3) AND ((Table2.ID)=15)) AND ((Table3.ID)=6)" & _
And so on to the end of the array/table.

Im a bit of a novice programmer and Im thinking this should be quite straightforward, Im just not sure exactly how to proceed.

I think the WHERE clause will end up looking something like this;

" WHERE ((((Table1.ID)=" & MyArray(1,1) & ") AND ((Table2.ID)=" & MyArray(1,2) & ")) AND ((Table3.ID)=" & MyArray(1,3) & ")" & _

Im just not sure of how to write the loop to pick up the right values and where it needs to sit in the procedure to make it function correctly.

Any ideas?

[Tinbendr]

I've created a table listing all the required combinations from a query I constructed in Access.
I would make that your first query call. I would add a new sheet, add the data to it, and loop through the rows.

I would make two macros. One to populate the query parameters and the other to pull the statistical data. (I've been bitten too many times working with dynamic data. I would import it. Take a quick look to make sure the parameter data imported ok, then proceed with the other.)

David