Automate pass-through query to run with different criteria and append to table

[duckie123]

Hi Everyone,

I have a pass-through query that pulls data for a certain cluster number from the DB2 IBM database using an OBDC. I can only run the query for two group numbers at a time since there is a large amount of data that is returned. I also have a make table query and append table query. What I would like to be able to do is connect to the OBDC DB2 server, run the pass-through query for the first cluster number, create a table with the make table query, run the pass through query again with second group number, and append this to the table from the make table query and so forth until I have gone through the list of cluster numbers.

I have a list of cluster numbers: (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20….etc.) which I need to run the query on and append to the table.

My pass-through query looks something like this:
SELECT DISTINCT VEND_ID, STATE, SALES, COST
FROM SALES_TABLE
WHERE CLUSTER_NUMBER IN (1,2)
AND (STATE= ‘CALIFORNIA’ AND VEND_ID IN(‘11’)
OR (STATE= ‘OREGON’ AND VEND_ID IN(‘12’)
OR (STATE= ‘WASHINGTON’ AND VEND_ID IN(‘13’)
OR (STATE= ‘NEVADA’ AND VEND_ID IN(‘14’)
OR (STATE= ‘ARIZONA’ AND VEND_ID IN(‘15’)
OR (STATE= ‘IDAHO’ AND VEND_ID IN(‘16’)
OR (STATE= ‘UTAH’ AND VEND_ID IN(‘17’)
OR (STATE= ‘TEXAS’ AND VEND_ID IN(‘18’))

My make table query code looks like this:
SELECT passthrough_query.*INTO Sales_Data
FROM passthrough_query

My append table query looks like this:
INSERT INTO Sales_Data (VEND_ID, STATE, SALES, COST)
SELECT passthrough_query.VEND_ID, passthrough_query.STATE, passthrough_query.SALES, passthrough_query.COST
FROM passthrough_query

If anyone has some ideas of how to do this in VBA, or offer some help to get this automated, I would really appreciate it. Thanks!

 

[SydneyGeek]

This makes some assumptions:
1. You will have a table called ClusterTable with a single number field called Cluster. into this put all of the cluster values that you want to loop through.
2. The Vendor_ID field is text. If it's a number field remove the single quotes.
3. The query names will need adjusting. Save the make-table and append queries; they are more convenient that way (and will often run faster than a straight SQL statement because they will compile)

You will also need to set a reference to the Microsoft DAO 3.6 Object Library.

Sub PassThroughLoop()
    Dim sSQL As String
    Dim rst As DAO.Recordset
    Dim lngCounter As Long
    
    lngCounter = 0
    Set rst = CurrentDb.TableDefs("ClusterTable").OpenRecordset
    
    With rst
        .MoveFirst
        Do Until .EOF
            lngCounter = lngCounter + 1
            sSQL = "SELECT DISTINCT VEND_ID, STATE, SALES, COST " _
                & "FROM SALES_TABLE " _
                & "WHERE CLUSTER_NUMBER=" & rst!Cluster _
                & " AND (STATE= ‘CALIFORNIA’ AND VEND_ID='11') " _
                & "OR (STATE= ‘OREGON’ AND VEND_ID='12') " _
                & "OR (STATE= ‘WASHINGTON’ AND VEND_ID='13') " _
                & "OR (STATE= ‘NEVADA’ AND VEND_ID='14') " _
                & "OR (STATE= ‘ARIZONA’ AND VEND_ID='15') " _
                & "OR (STATE= ‘IDAHO’ AND VEND_ID='16') " _
                & "OR (STATE= ‘UTAH’ AND VEND_ID='17') " _
                & "OR (STATE= ‘TEXAS’ AND VEND_ID='18');"
            CurrentDb.QueryDefs("qryPassThrough").SQL = sSQL
            If lngCounter = 1 Then
                DoCmd.OpenQuery "qryMakeTable"
            Else
                DoCmd.OpenQuery "qryAppendRecords"
            End If
            .MoveNext
        Loop
    End With
    
    rst.Close
    Set rst = Nothing
End Sub

Denis

 

[duckie123]

Hi SydneyGeek,
Thank you so much for responding!! I am able to get to the part where it asks if I want to create a make table and then when I click Yes, I get the message: Run-time error '3146' ODBC-call failed. "An unexpected token "." was found following "". Expected tokens may include: ", FROM INTO". Do you have any idea how I would be able to resolve this issue? Thanks!

 

[james_lankford]

probably have to see the sql for the


DoCmd.OpenQuery "qryMakeTable"
DoCmd.OpenQuery "qryAppendRecords

also, the quotes around your states don't look like the right kind of quotes,
see how they look different from the quotes around the vend_id ?

 

[duckie123]

Hi James,
My queries look like this (I didn't make any changes to it since the original post):

My make table query code looks like this:
SELECT passthrough_query.*INTO Sales_Data
FROM passthrough_query

My append table query looks like this:
INSERT INTO Sales_Data (VEND_ID, STATE, SALES, COST)
SELECT passthrough_query.VEND_ID, passthrough_query.STATE, passthrough_query.SALES, passthrough_query.COST
FROM passthrough_query

I removed the single quotes from the VEND_ID because I read that for numbers they are not necessary.

I have tried to play around with it and keep getting to the part where it says: "You are about to run a make-table query that will modify data in your table." After I click Yes, it gives me the Run-time error '3146' ODBC-- call failed. The data types of the operands for the operation "" are not compatible. I am guessing it is specific to DB2 but am not sure what it is. If you have any more suggestions, I would appreciate it. Thanks!