?
Solved

Excel database queries disappear

Posted on 2003-02-26
7
Medium Priority
?
353 Views
Last Modified: 2008-03-17
I have a workbook with 6 sheets containing SQLServer queries.  Occasionally the queries become static data, and cannot be refreshed/edited, etc.  The only way I can get the connections back is to recreate the queries from scratch, a hassle.  Why is this happening?
0
Comment
Question by:stathamj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 

Accepted Solution

by:
ferg_IRL earned 400 total points
ID: 8032160
Hi stathamj, the same thing happens to me every so often. The queries don't actually disappear at all, just the range gets a bit messed up.

On the "Insert" menu, got to "Names" and then "Define". This will give you a list of all the defined ranges. In here somewhere you should see a range called "ExternalData..." with some number replacing the dots.

If you click on this, the range it refers to should appear at the bottom of the dialog box. I suspect that in this range there will be a big space between the "=" and the start of the range. If you remove this space, your problem should be solved!

Ferg
0
 

Author Comment

by:stathamj
ID: 8034730
The external data ranges are called "Query_from_(my DSN name)" and when I selected one that had problems, there was one space after the "=".  I removed it and the problem remained.
0
 

Expert Comment

by:ferg_IRL
ID: 8034846
I don't mean to sound dodgy here but are you sure it was just one space? The first time that happened to me and somebody told me what to do, I thought I'd removed all the spaces but there was actually one remaining which caused it to still appear to have lost the data. The solution should lie somewhere in that named range anyway.

Ferg
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:stathamj
ID: 8035424
It *may* have been two, but I don't think so, and no more than that.  I definitely removed all of them, one way or another.  It is very helpful information; I didn't realize external data would show up in the named ranges.  Also, I've only been able to reproduce the problem once, so I'll keep at it.  Thanks!!
0
 

Author Comment

by:stathamj
ID: 8035501
I stand corrected!  I reproduced the problem one more time, removed *two* spaces, and it fixed the problem.  Sorry about that, and thanks very much for your help.
0
 

Expert Comment

by:ferg_IRL
ID: 8040972
Pleasure. The external data shouldn't appear in those named ranges, it only seems to when it gets lost from the sheet as heppened to you here. As for an explanation why it happens, I've got no idea!
0
 

Author Comment

by:stathamj
ID: 8042702
As it turns out, I've had mixed success with this fix.  Occasionally, I'll remove spaces, and the problem will persist, other times it will work perfectly... Strange.  Thanks again.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question