PDA

View Full Version : Batch processing SQL query strings



finnbarr99
09-14-2010, 09:08 AM
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?

xlbo
09-20-2010, 05:39 PM
Where your loop sits is dependant on how long this will take to run. The most effiecient way is to only open the connection once but if this will take a long time, you may run into issues with connection timeouts

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:\CustDbase.accdb"
Dim Rng as range
Dim i as long
rng = worksheets("SheetName").Range("A1")
i = 1
Do while rng.offset(i,0) <> ""

MySQL = "SELECT Fields FROM Tables WHERE Table1.ID=" rng.offset(i,0) & "AND Table2.ID=" & rng.offset(i,1) & "AND Table3.ID=" & rng.offset(i,2) & "ORDER BY Whatever"

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Dump data onto sheet
Sheets("SheetName").Range("A1").copyfromrecordset MyRecordset

'process data

'release recordset from memory
set MyRecordset = nothing
i = i + 1

Loop