Disable Background Query results in Failing Download (PQ)

[Niditro]

Hi, I have an issue with some given VBA code that refreshes the PQ connection to an Access query, but first sets BackgroundQuery to False, in order to be able to wait for the connection to be refreshed and then continues the code.

However, when background refreshing is turned on, the query runs fine and data is loaded to the worksheet, but when I turn off the background refresh, the query mentiones: "Download Failed".
When I run the code manually and keep pressing F5 on error, then in the end (after three error messages) it has downloaded the data. But when I add "On Error Resume Next" the it doesn't download the data. So it looks as if the error message is a bug, because when forcing the code to continue in the end the data is refreshed. Is there a way to simulate the "Debug - F5" combination in VBA, since "On Error Resume Next" is not working.

Also when I manually disable background query and click Refresh All then I get the same error.

I searched whole internet for similar issues and tried many tips and tricks but it still isn't solved.
Please find attached print screens too.

Hope someone can help me out here!?

Best Regards, Nico

 

[jmacleary]

Hi and welcome to the forum. I think you are suffering from the fact that, despite what you expect, your code will actually be carrying on executing after objConnection.Refresh. Add this line of code straight after:

objConnection.Refresh
Application.CalculateUntilAsyncQueriesDone

 

[Niditro]

Hi John, thanks for your very swift response! I followed your suggestion, but the code runs in error on obj.Connection.Refresh as shown in my earlier attached print screen, so it doesn't even come to the extra line of code unfortunately...

 

[Niditro]

Very strange too: if I enable BackgroundQuery and run below code no issues appear.

Sub Refresh()

Dim Conn As WorkbookConnection

    For Each Conn In ThisWorkbook.Connections
       Conn.Refresh
    Next Conn

End Sub

But that prevents me from informing the user about the status of the refresh. (Besides the very small indication in the status bar, which nobody ever notices )

 

[RoryA]

Did you try John's code with the background query enabled?

 

[Niditro]

Now I did; resulting in an unresponsive workbook.....

 

[jmacleary]

OK. Leave background query enabled and move the
Application.CalculateUntilAsyncQueriesDone

line down to under the Next line. The code will still wait for all refreshes, but with background query enabled they should all be running at once so it will be less of a wait (depending on whether the queries can run concurrently or not). At the end of the day, if you want the user to be told when all queries have finished, thats what you'll have to do.

 

[Niditro]

Sorry John, but when I add your line of code below Next, then my workbook becomes unresponsive.

Sub Refresh()
'
' Refresh Macro
'
' Keyboard Shortcut: Ctrl+Shift+R

Dim Conn As WorkbookConnection

For Each Conn In ThisWorkbook.Connections
      
       Conn.Refresh

Next Conn

Application.CalculateUntilAsyncQueriesDone

End Sub

All runs fine without your line of code added, as I mentioned above, but when BackgroundQuery is enabled I have no possibility to inform the user like "One moment please, the data is refreshed"

The idea is that in Sheet 1 (INPUT) the user is able to put his data, then he switches to sheet 2 (OUTPUT) where, after the query has run, he will see his data but now returned with the data from the Access Query. Fetching this data sometimes can take a second or 10, which may leave the user confused, since he doesn't see anything changing. Therefore I want to show a message or at least showing an hourglass to indicate that excel is working on it. This is only possible when BackgroundQuery is disabled. But disabling that causes only issues on refreshing in my case.

 

[jmacleary]

OK sorry I understand the problem a bit more now. It is possible to put messages in the statusbar at the bottom (but as you say, most users never look at that).How about have a cell on the destination sheet (OUTPUT?) that tells the user whats going on. Replace my 1 line with something like thisyou can make A1 BIG and BOLD and ITALIC or whatever you think will be best for the user to see.

Sheets("OUTPUT").Select
Sheets("OUTPUT").range("A1").value="Please wait while up to date data is collected....."
Application.CalculateUntilAsyncQueriesDone
Sheets("OUTPUT").range("A1").value="Done"

The code will still stop executing till all the refreshes are done, but he will be looking at a line that says its working.

 

[Niditro]

Hi John, I tried different options, but as soon as I add "Application.CalculateUntilAsyncQueriesDone" the whole workbook gets unresponsive. Even CTRL-BREAK doesn't work then. I have to close the workbook.
Apparently there is something very wrong under the hood...
So I'm back at the start: I'm only able to refresh the data with BackgroundQuery enabled and just refresh the connection....
Maybe a bug?