Losing QueryTable on Refresh - Excel 2003

david · External Since: Aug 25, 2004 Posts: 43

I hope this is posted in the right place...

I've got a few spreadsheets that I've set up which retrieve data from
External SQL Server databases - I manually refresh the various tables by
right clicking in the data region and choosing 'Refresh'. I've been doing
this for a few years now and consider myself to be fairly proficient at it
and haven't seen this problem until recently.

In the last few weeks, I've had issues where the Data Range (QueryTable)
would disappear sometime after I refresh the data. This doesn't happen
everytime - maybe only 25-40% of the time. When the problem happens, the data
refreshes and at some point the data region disappears. After that, you can
no longer right click in the region. If the file is then saved and
re-opened, the query is still gone. It is as if the 'Save Query Definition'
is turned off. (I rarely change this option as I need to re-run the same
queries often.)

To eliminate the question of corrupt Excel files, I set up a quick query in
a new workbook and saved it. I then ran through a series of refreshes
(10-20) while also changing between open applications (Word, Access, Outlook,
IE, etc). After a few minutes, the query disappeared from this workbook also.

I'm running Excel 2003 (SP1) under Windows 2000 (SP3). As far as I know
(this is a company managed computer), the only updates to my system have been
MS updates.

Sorry for the length of this message.

Thanks in advance for any help.

Bill Manville · External Since: Jul 30, 2004 Posts: 539

Just a hunch
Could it be that you are using background refresh and the refresh is
not completing?
Does it still happen if you un-tick the Enable background refresh box
on the querytable Properties dialog?

Bill Manville
MVP - Microsoft Excel, Oxford, England

david · External Since: Aug 25, 2004 Posts: 43

Thanks, Bill, but that's not it. I've got a few different queries and most of
them return within a second or two. I do use the Background Refresh almost
exclusively so I know what the right-click menu shows during a refresh - the
data refresh options are still available, but most are dimmed while the query
is processing.

In my situation, the data will be returned by the query, but subsequently,
the right -context menu will no longer have any of the data query options.
It looks like the query definition is being deleted.

One other point: It looks like the named range automatically created by the
data query ("Query_from_xxxx") still exists and points to the data range.
Also, if you clear cells that have a query range defined, Excel will ask if
you want to delete the query definition also. After I lose my queries, you
can clear the cells without any prompt from excel.

I hope that helps more.

Thanks.

DM Unseen · External Since: Jun 21, 2005 Posts: 25

It seems XL is loosing the connection anyway. Pls use VBA to inspect
the QueryTable.
Maybe try
http://homepages.paradise.net.nz/~robree/excel/queryeditor.html to
inspect your QueryTable

DM Unseen

david · External Since: Aug 25, 2004 Posts: 43

Thanks for the suggestion. That's a pretty cool tool; I'll probably be using
that more frequently.

Everything looks ok with the query using the tool. The problem that I run
across is pretty random. On some spreadsheets, it's about 30% of the time
after the refresh that the table is lost, on others, it's more frequent. I
don't know if it's related to me switching application windows after the
query runs or what.

If it was only my computer, I'd think it was something weird with my setup,
but this is happening with a coworker's computer also. He's got a similar
configuration (he has WinXP vs. my Win2000, but everything else is similarly
configured).

Our help desk wants to reload the system image to my hard drive. I concede
that this 'might' work, but think it's got to be related to something.

Keep the suggestions coming... (please).

Thanks.

david · External Since: Aug 25, 2004 Posts: 43

By the way, how do I directly access this newsgroup within Microsoft's Forum
Setup (microsoft.public.excel.querydao)? The querydao subtopic isn't listed
when I enter MS newsgroups (using support.microsoft.com, then choosing 'Self
Support Options', then 'Microsoft Newsgroups'. I then navigate down the
English - Office - Excel path to search for 'QueryTable').

I'm sure that's a newbie question - sorry if duplicate.

david · External Since: Aug 25, 2004 Posts: 43

Ignore the last question - I found the direct link to this NG.

Is there a better forum to post my question? Any ideas?

Thanks.

"david" wrote:

> By the way, how do I directly access this newsgroup within Microsoft's Forum
> Setup (microsoft.public.excel.querydao)? The querydao subtopic isn't listed
> when I enter MS newsgroups (using support.microsoft.com, then choosing 'Self
> Support Options', then 'Microsoft Newsgroups'. I then navigate down the
> English - Office - Excel path to search for 'QueryTable').
>
> I'm sure that's a newbie question - sorry if duplicate.
>

DM Unseen · External Since: Jun 21, 2005 Posts: 25

Nope this is pretty much the best NG, the experts on Querying and Excel
are not just flocking to this space.(I'm not sure there are that many
though).

My first suggestion is that the ODBC connection to your DB crashes in a
nasty way. This would suggest updating your ODBC drivers: Download the
latest mdac from MS. Pls use another tool to run the queries (MS access
e.g. by linking a table through an ODBC connection to SQL server and
see if anything strange shows up. Also do not forget to run Office
Update regulalry.

Besides this there could be a general network issue.

DM Unseen

Randy · Joined: Apr 06, 2006 Posts: 1

Hi David,

Did you ever find the reason or cause for the query disapperaing?
I'm experiencing the exact same behavior.
On the sheet with the missing query, if I go to Insert | Name | Define and select the query name, in the 'Refers To' box the range has 3 spaces between the 'equal sign' and the range. This is the only thing I see unusual about the sheet.

thanks
-randy

bizopsaz · Joined: Dec 22, 2006 Posts: 3

David, I have had the same problem with my queries for the last year. I've used Excel for years and only with Excel 2003 has this occurred. Did you find a resolution? I'm still trying to figure it out.

Mel.

bizopsaz · Joined: Dec 22, 2006 Posts: 3

It appears to be memory or cache related (?). I have this problem with certain refreshes, particularly if I have more than one I need to run in one workbook. I found that I don't have a problem if I refresh the first query, save the workbook, exit EXCEL (not just the workbook), open the workbook and refresh the second query. If I refresh without closing excel, my second query is lost.

fyz · Joined: Feb 28, 2008 Posts: 29

Hi,

Just try a popular Excel file recovery tool called Advanced Excel Repair to repair your Excel file. It is a powerful tool to repair corrupt or damaged Excel files.

Detailed information about Advanced Excel Repair can be found at http://www.datanumen.com/aer/

And you can also download a free demo version at http://www.datanumen.com/aer/aer.exe

Alan

Hudges · Joined: Sep 17, 2009 Posts: 1

I had the same or a similar problem. The ODBC link was to a SQL Server Database .

It appears to have been resolved by saving the SQL Query within the Microsoft Query application.

Cheers

Hugh