Multiple Queries in workbook - best methods for refresh

[berlinhammer]

Hello,

I am using Excel 2003. I have a workbook which runs 7 large queries. Each query is in a separate worksheet and is triggered by a worksheet specific Command Button which generates SQL code which is then called by a subroutine which queries an Oracle database through an ADODB connection.

I am happy with the data this produces, the only annoyance I am finding is that I do not know how to go about automatically refreshing all 7 queries as at present I have to go through manually clicking the buttons one by one to get my whole workbook updated. It is quite time-consuming.

I believe that querytables are one way of being able to refresh automatically. Are these compatible with ADO? Would anyone have any advice on the best way to proceed?

I'll attach the code I use at present as an FYI. Grateful for any advice or wisdom!!

Thanks

Jon

In a worksheet object:

Private Sub CmdRunReport_Click()

Dim sqlString As String

sqlString = "select * " & _
            "from (select * " & _
                  "from " & _
                  "reporter.jonf_gsfportfolio " & _
                  "Union " & _
                  "select * " & _
                  "from " & _
                  "reporter.jonf_gsfport_pivotrows) " & _
            "where " & _
            "deal_geo_seg not in 'NorthAmer'" & _
            "order by " & _
            "deal_geo_seg " & _
            ",deal_bus_grp " & _
            ",deal_legal_name" & _
            ",class_name " & _
            ",rtng_rn"
   
    Call ThisWorkbook.GetData(sqlString)

End Sub

In the thisworkbook object:

Public Sub GetData(sqlString)
    Dim conn As New ADODB.Connection
    Dim ConnString As String
    Dim iCols As Long
    Dim rsRecords As New ADODB.Recordset

    ConnString = "DSN=xxx;Uid=xxx;Pwd=xxx"

    conn.CommandTimeout = 0
    conn.ConnectionTimeout = 0
    conn.Open ConnString
    rsRecords.CursorLocation = adUseServer
    rsRecords.Open sqlString, conn, adOpenForwardOnly, adLockReadOnly

    If conn.State = adStateOpen Then
        ActiveSheet.Range("report_content").CurrentRegion.ClearContents
        ActiveSheet.Range("report_content").CopyFromRecordset rsRecords
      
        For iCols = 0 To rsRecords.Fields.Count - 1
            ActiveSheet.Range("report_header").Cells(1, iCols + 1).Value = rsRecords.Fields(iCols).Name
        Next
    Else
        MsgBox "no connection"
    End If

    rsRecords.Close
    Set rsRecords = Nothing
    conn.Close
    Set conn = Nothing
End Sub