Excel wont refresh until VBA code stops running

[Carbon1198]

Hi All,

I have an Excel file that executes multiple queries against an SQL Database using ODBCConnection

The code works perfectly when run sequentially, one after each other
However I would like to try run two queries in parallel (at the same time) to make the overall code execute faster


I can do this by setting the ‘BackgroundQuery’ connection property to true (enabling one query to run in the background)

Example:
Query #1: ActiveWorkbook.Connections(“Query_1”).ODBCConnection.BackgroundQuery = True
Query #2: ActiveWorkbook.Connections(“Query_1”).ODBCConnection.BackgroundQuery = False

This works great … However…

Excel does not update / refresh / display the results until after the code stops running (or runs to a break)
Excel will only ‘refresh’ (display) results from queries where BackgroundQuery = False
Excel will NOT ‘refresh’ (display) the query results where .BackgroundQuery = True

Application.ScreenUpdating -or- DoEvents does not work

Any ideas on how to ‘force’ Excel to refresh / release data while the code is still running..? Is this even possible..?


As a side note (and similar problem)… I have seen this same behaviour when trying to update the Object.BackColor of an ActiveX control command button.
The button colour would update perfectly when stepping (F8) through the code – But would not update during ‘running’ code execution.

Any insight into why this happens (or how to work around it) greatly appreciated

 

[Michael M]

From your desciption....I'd suggest it isn't possible.
Once the code is controlling a task, it won't do anything else !!