Problem refreshing large number of Access queries in workbook

[ChrisChuffy]

I have a workbook/report that is fed from ~200 queries to an access database. we recently added a number of queries and we have started to get odd behaviour when the code gets to the 120-150 query range in that it starts prompting for a data source.

I'm sure the problem is NOT the queries themselves since I can force the error to occur at different places just be rearranging tabs.

Is there a way to monitor the number of query events via the code and force them to complete before the VBA continues?

Or, can you think of a better way to not have more than ~100 query refreshes active at a time?

Note: Due to the nature of the report it is not feasable to either split the report up or restructure the queries so I need fewer of them.

Thanks for any input!

Private Sub cmd_refresh_all_now_Click()
    ' Refresh all queries in the workbook.  This works even when
    ' automatic refreshes have been disabled.
 
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim cnt As Integer
    cnt = 1
    For Each ws In ActiveWorkbook.Worksheets
        For Each qt In ws.QueryTables
            Debug.Print " Query count: & & cstr(cnt)"
            Debug.Print "   Worksheet: " & CStr(ws.Name)
            Debug.Print "       Query: " & CStr(qt.Name)
            Debug.Print "------------------------------"
            qt.Refresh
            DoEvents
            cnt = cnt + 1
        Next qt
    Next ws
End Sub

 

[Richard Schollar]

If you call Refresh like the following:

qt.Refresh BackgroundQuery:=False

then the refresh action will be completed before code execution continues to the next line.

 

[ChrisChuffy]

That did it, thanks!