VBA - Retry SQL Query if Query Fails

I have multiple workbooks for my organization that are connected to an external data source via SQL query. Every day, I go in and refresh these workbooks. To try and alleviate some of the manual process behind this, I created a simple VBA sub that takes a list of workbook paths from cells on a sheet, opens those workbooks, refreshes all, saves, and moves on to the next one on the list. Here's part of the code:

Please Login or Register  to view this content.

The RefresherWB, which contains the list and code, has a field that is updated with success/fail if something were to happen to cause it to fail. However, there are times where for whatever reason (our data warehouse sucks) the SQL query will not complete successfully. Currently the RefresherWB goes through its commands normally and gives it a "success" because obviously nothing went wrong from the VBA perspective.

Does anyone know if there is a way to pull some kind of error code directly from the SQL query fail? That is, I would either like to retry the refresh all command on the current workbook being refreshed if the SQL query fails, or I would at least like to be able to put an indicator in my RefresherWB that the query in that workbook failed. As it is now, I still have to manually open each workbook to verify the data source updated successfully.



Thanks in advance.

That's a decent idea, I could check the number of rows before and after and if they match, retry the refresh until the numbers differ or it reaches some limiting number of attempts. I'll give that a try. Thanks!