Refresh Query Table causing debug error with Background Refresh pr

[Gum]

I have query tables in a worksheet that I wish to get refreshed each time a
macro is executed. The query tables are set to refresh data from SQL
database every minute (via Data...Connections...properties). However, when
the macro is executed, there is an error whenever the background refresh
process is taking place. Is there some way of getting around this eg.
knowing whether the background refresh process is taking place and starting
the refresh process after?
Is there a better refresh macro that will only refresh the query table and
not the entire worksheet?
Macro:
sub Rfresh()
ActiveWorkBook.RefreshAll
end sub

 

[FSt1]

hi
it has been my experence never to have a query refresh in the background
perticularly if a macro is running and very perticularly if the macro may be
using the data being refreshed. you didn't say how many queries you have and
i think the refreshall command may be the source of your problems.(i've had
problems with that) refreshing every minute may also be a contributing
factor if you have a large number of queries.
i would increase the amout of time between refreshes if possible and refresh
each query 1 at a time and NOT in the background.

sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false

you would need a refresh line like above for each query.

Regards
FSt1

 

[Joel]

If you haved an SQL error (the command text line) you will always get the
error on the refresh line. There is no error checking until you execute the
refresh. Here is how you debug the problem.

There are two tricks
1) Record a macro while performing the query manually. Start macro
recorder by going to worksheet Tools - Macro - Start Recording. The perform
the query manually from menu Data - Import External Data - then select the
type of query you want to perfrom. Then stop recording and use the recorded
macro.


2) The recorded macro may not give you all the options you require. So
here is the 2nds trick. Do a query with no options. Just get the query to
work by using the worksheet menu Data - Import External Data. Now edit the
query. Click on a cell where the query returned data. Then go back to the
Menu Data - Import External Data. Now there is an option to Edit Query. Get
the query editor and press the SQL button. The command text is the SQL
statments. Put the SQL options in one at a time. The SQL editor will give
you good error messages to help you isolate your problems.


Note: When you perform a manual query the last wizard menu give you the
option of editing the query. The edit option get you to the same tool as
Edit Query option in the menu Data - Import External Data.