Problem refreshing large number of Access queries in workbook

ChrisChuffy

New Member
Joined
Nov 18, 2008
Messages
15
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!

Code:
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
If you call Refresh like the following:

Code:
qt.Refresh BackgroundQuery:=False

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

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top