Refresh of MS Query breaks query

george1234

New Member
Joined
Oct 5, 2006
Messages
2
I have some xls sheet that contain queries to a SQL database.

I find that sometimes when i refresh the query, the query breaks/dies after the data has been pulled from SQL. What I mean is that I am no longer able to refresh the query.

I am left with the need to create the query again. I do not know why this happens although it seems to have something to do with whether i have re-calculated all data in the spreadsheet prior to refreshing the data. Also it seems to happen less for queries that do not have formula column within the query output columns and/or formulas in columns adjacent to the query output columns.

Has anyone run into this problem? How can I stop this from occurring?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi George

I posted a similar message a few months ago - got no response though. Often, you'll find the named data range ends up with a few spaces before its text starts (Insert>Name>Define and look at the list). Don't know why it happens and don't know how to fix it.

I did consider that it usually happened when the query was interrupted in the course of normal execution (eg because the computer trying to run the query had too many spreadsheets open which caused the whole lot to lock up - shutting down and going back in sometimes resulted in the query being lost).

It is almost as if the option to "Save Query Definition" was getting cleared for no apparent reason.

The workaround I devised was to hold the query definition within a macro (so that if the definition appeared to have disappeared so-to-speak, it could be reactivated by clicking the macro).

Best regards

Richard
 

george1234

New Member
Joined
Oct 5, 2006
Messages
2
Hi Richard
thanks for your comments.
i will let you know if i come across anything useful in the meantime
 

Forum statistics

Threads
1,136,588
Messages
5,676,675
Members
419,643
Latest member
ishanalikhan

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
Top