Results 1 to 2 of 2

Thread: Batch processing SQL query strings

  1. #1

    Batch processing SQL query strings

    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?

  2. #2
    VBAX Regular xlbo's Avatar
    Aug 2006
    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



    We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts