1) I know the prefered way to set up external connections to databases is
ADO.
2) I used the wizard <import external data<New Database Query
Next I create a <New Data Source using the oracle ODBC Driver (which is
irrelevant for the question)
Q1: Where is this data source saved ?
3) Next I come in MS Query and I can type the sql and return the data to
excell.
4) I come in a dialog box which asks me where to put the data. In this box
I also got the options buttom which allows me to set some properties for the
query and to save the definition of the connection in the sheet (Save Query
Definition).
So far so good.
Q2: Can I distribute this sheet to different users whithout having the need
to create an odbc connection on their computers ? (they must be able to
refresh the data)
Q3: How do I change the connection string (not programmaticly) if the name
of the database changes ? If I go to the <Data Range Properties, there is
no option to change the connection string and if I use <Edit query I connect
directly to the database and have no options to change the connection string.
However, since the connection is saved in the sheet, it has to be somewhere
(to my feeling). And since I managed to get it in by wizards, there must be
an option to change it whithout writing code (or did our friends of MS forgot
this :-) ).