locked
ListObject QueryTable refresh from Recordset in the loop RRS feed

  • Question

  • Hi.

    PIs it possible to refresh few querytables from the same Recordset in the loop without Requery this recordset?

    My code is:

    rs.Open strSQL, cn
    
    Dim ws As Worksheet
    Dim Table As ListObject
    Dim qTable As QueryTable
    For Each ws In Worksheets
        If ws.Listobjects.count> 0 Then
            Set Table = ws.ListObjects(1)
            Set qTable = Table.QueryTable
            With qTable
                 Set .Recordset = rs
                rs.Requery 'without this line code works as described below. With this line code works fine
                 .Refresh False
             End With
          End If 'counter
    Next ws

    Result.

    Only first QueryTable is fully refreshed.All others gets only proper fields from the recordset but no row is transferred from rs to table.

    If I chang order of sheets with tables first one is refreshed and this one what was refreshed properly earlier gets cneaned-up (no records shown, all gone...)


    Monday, April 9, 2018 10:12 AM

All replies

  • hello,

    if you loop every listobject, you will refresh every querytable:

    For Each ws In Worksheets
        For Each Table In ws.ListObjects
            Call Table.QueryTable.Refresh
        Next
    Next ws

    is this what you want?

    Monday, April 9, 2018 3:06 PM
  • Hi.

    No.

    I know how to refresh in the loop and my code is similar to yours. Problem is: I want to go to DB just ONCE to get Recordset and then refresh data in Querytables from this recordset. Formally Recordset doesn't change when I refresh first QueryTable. But secund QueryTable get no records. 

    Try my code in the workbook that has 2 querytables created with use of " set source:=recordset" and say how it works for you 

    My Querytables are not directly connected to DB. They should read data from RS. 

    Tuesday, April 10, 2018 12:02 PM
  • Hello,

    Is it work for you?

    If ws.ListObjects.Count > 0 Then
            For Each Table In ws.ListObjects
            Set qTable = Table.QueryTable
            With qTable
                 Set .Recordset = rs
                 rs.Requery
                 .Refresh False
             End With
             Next Table
          End If '

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 12, 2018 8:51 AM
  • Hello,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 16, 2018 4:53 AM
  • 1 YES, Requery works but (see below)

    2. NO there is no straight-forward solution. Recordset can't be "reused"

    I suspect that is a bug in the library as I don't see any reason that recordset can be read only once. Especially that rs.clone doesn't help (before the loop I create RS_1 and in the loop @ the beginning I do SET RS_2=RS_1.clone).

    I'm not sure is the problem in recordset or Excel that allows to read recordset only once.
    My temporary workaround is to use rs.requery (as in your code) in the loop before every next attempt to use .recordset property of Querytable.

    But then Excel reads the same data from SQL Server many times.

    Currently I'm focused on the solution to change SQL SP to something that uses cash or "work" or "temp" table to not calculate result every time.

    In past I was copying from one QueryTable to other ListObjects column by column, but with larger data it is very slow.

    I tried to use this first ListObject with QueryTable as source for other ListObjects querytables but it is not stable (sometimes works , sometimes not).

    Monday, April 16, 2018 8:08 AM