asked on

Excel database queries disappear

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?

ASKER CERTIFIED SOLUTION

ferg_IRL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stathamj

ASKER

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.

ferg_IRL

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

stathamj

ASKER

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!!

stathamj

ASKER

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.

ferg_IRL

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!

stathamj

ASKER

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.