none
How to Refresh Queries with VBA

    Question

  • I inserted several ODBC queries into Excel; all point to an Access DB sitting on the network.  The Connections was set up as such:  From Other Sources, From Microsoft Query, From MS Access Database, then navigate to the appropriate table in Access.  I’m trying to update these connections with VBA.  I have this:

    Sub RefreshQueries()

     

    Dim ws As Worksheet

    Dim qry As QueryTable

     

    For Each ws In ActiveWorkbook.Worksheets

      For Each qry In ws.QueryTables

        ws.Select

        Debug.Print ws.Name

            qry.Refresh

      Next qry

    Next ws

     

    Set qry = Nothing

    Set ws = Nothing

     

    Range("F3") = Now()

    Application.Calculate

    End Sub

     

    One query fails on this line:

    qry.Refresh

     

    If I comment that out, and hit F8, I can loop through the rest of the code, but no queries get refreshed.  Somehow, the qry.Refresh doesn’t seem to be fully qualified (although that code works fine in another workbook.  Also, only one of the 6 queries seems to be a QueryTable, but all were set up the same way.  Is it possible that these queries are of another type?  Perhaps something other than a QueryTable?

     

    Thanks! 

    Tuesday, February 01, 2011 10:49 PM

Answers

  • I would try the following:

    Dim ws As Worksheet
    Dim qry As QueryTable
     
    For Each ws In Thisworkbook.Worksheets
     Debug.Print ws.Name
     For Each qry In ws.QueryTables
        Debug.Print qry.Name
        Debug.Print qry.connection
        Debug.Print qry.commandtext
        qry.Refresh (false)
     Next qry
    Next ws
     
    Set qry = Nothing
    Set ws = Nothing
     
    Range("F3") = Now()
    Application.Calculate
    End Sub
    

     

     

    the code you have should work - issue is probably with a malformed connection string or SQL statement

    Also, what error do you get when the code bombs?


    Rgds Geoff
    • Marked as answer by RyanShuell Friday, February 04, 2011 3:30 PM
    Wednesday, February 02, 2011 1:56 AM
  • that error implies an issue with a connection string somewhere

    Have you at any point either moved the access database, changed a table name, changed the version of access or tried to amend the conenction string to the database via code or manually?

    Whatever the case, I would be tempted to macro record yourself setting up a connection to the database. Take the connection string generated and use something like

    activesheet.querytables(1).connection = "the generated string here")

    amend the 1 for the appropriate querytable and the connection string for the appropriate table in the database


    Rgds Geoff
    • Marked as answer by RyanShuell Friday, February 04, 2011 3:30 PM
    Thursday, February 03, 2011 2:30 AM
  • Ryan : I notg sure which version of excel you are working with and if the version of excel you create the queries is the same version of excel that you are using to refresh the queries.

     

    In excel 2003 you had query tables.  In 2007 excel create List Tables.  So yo may think you have query tables when they are list tables.


    jdweng
    • Marked as answer by RyanShuell Friday, February 04, 2011 3:30 PM
    Thursday, February 03, 2011 2:36 AM

All replies

  • I would try the following:

    Dim ws As Worksheet
    Dim qry As QueryTable
     
    For Each ws In Thisworkbook.Worksheets
     Debug.Print ws.Name
     For Each qry In ws.QueryTables
        Debug.Print qry.Name
        Debug.Print qry.connection
        Debug.Print qry.commandtext
        qry.Refresh (false)
     Next qry
    Next ws
     
    Set qry = Nothing
    Set ws = Nothing
     
    Range("F3") = Now()
    Application.Calculate
    End Sub
    

     

     

    the code you have should work - issue is probably with a malformed connection string or SQL statement

    Also, what error do you get when the code bombs?


    Rgds Geoff
    • Marked as answer by RyanShuell Friday, February 04, 2011 3:30 PM
    Wednesday, February 02, 2011 1:56 AM
  • I tried this code and I get a run-time error 1004:

    Incomplete Datasource.

    I get the same error with my code.  I've used code just like this before, without a problem.  I still can't determine what the issue is.  Any other ideas, Geoff, or someone else?

    Thanks!

     

    Thursday, February 03, 2011 2:14 AM
  • that error implies an issue with a connection string somewhere

    Have you at any point either moved the access database, changed a table name, changed the version of access or tried to amend the conenction string to the database via code or manually?

    Whatever the case, I would be tempted to macro record yourself setting up a connection to the database. Take the connection string generated and use something like

    activesheet.querytables(1).connection = "the generated string here")

    amend the 1 for the appropriate querytable and the connection string for the appropriate table in the database


    Rgds Geoff
    • Marked as answer by RyanShuell Friday, February 04, 2011 3:30 PM
    Thursday, February 03, 2011 2:30 AM
  • Ryan : I notg sure which version of excel you are working with and if the version of excel you create the queries is the same version of excel that you are using to refresh the queries.

     

    In excel 2003 you had query tables.  In 2007 excel create List Tables.  So yo may think you have query tables when they are list tables.


    jdweng
    • Marked as answer by RyanShuell Friday, February 04, 2011 3:30 PM
    Thursday, February 03, 2011 2:36 AM
  • In the end, a single line of code did everything I wanted to do:

    ActiveWorkbook.RefreshAll

    I guess my old code, and Geoff’s code, worked fine in Excel 2003.  In Excel 2007, things are different.  Thanks for the tip Joel.  I have to look into that a bit more.

     

     

    Friday, February 04, 2011 3:32 PM