can't change query definition in MSQuery

[scottgp]

Hi,
We've got "reports" in Excel 2002 that were created using MSQuery to
run queries against an Oracle database. Now we need to alter the
query. We modify the query, return the data to Excel, but the altered
SQL is not saved. Is this a bug? Shouldn't we be able to edit and
save the query?
I've used VBA before to change the connection property of pivot
tables but I didn't think VBA was necessary to modify the SQL.
Thanks,
Scott P.

 

[K Dales]

You certainly should be able to save the edited query witout having to use
VBA. I do it all the time and have never run up against the problem you are
having. I can't tell why it is happening or give a definitive fix, but one
thing I would try would be to go into MSQuery, make the changes, then save
the query (from inside MSQuery) as a query file. Perhaps just the act of
doing so will make Excel recognize that the query has changed - but if not,
at the very least you will have a saved copy of the query and, if necessary,
can rebuild the querytable by specifying the new query file.

Wish I had better advice to give you, but I can only give what I've got, and
in this case it's just speculation.
K Dales

 

[scottgp]

K,
Thanks for replying. Just to update you: I tried saving the query
but that didn't make a difference. However, some more testing
indicated that it seems to be ODBC driver specific. I inserted a new
sheet and created a query going against a SQL Server database. I was
able to subsequently modify the query and the changes were saved. I
then tried a newer version of the Oracle ODBC driver but it didn't make
a difference.
As a general gripe, I with MSQuery was better documented. Oh well.
Thanks,
Scott