Changing data source in MS Query 1

[DannyLondon]

Hello all

I have found this question asked a couple of times over the last two years, but have not found the thread coming to a good conclusion.

My situation is that I have an Excel 2000 workbook with external data, linking via ODBC to an Oracle database. The MS Query is embedded in the workbook, rather than being a separate .dqy file.

I would like to change the database that is used in the query. Changing the ODBC setting has no impact. When I do save the query to a .dqy, I can confirm that the connection parameters are all stored in the query, rather than referencing the ODBC setting.

Does anyone know how (or if) the database can be changed without having to build the entire query up again?

Thanks
Danny...

 

[SkipVought]

Danny,
Macro record editing your query. Then go into the code, clean it up and you will see where the dsn is defined et al. Just substitute a variable string and you'll be able to change the data source.

Hope this helps Skip,
Skip@theofficeexperts.com

 

[DannyLondon]

Skip

Thanks for your help, you get a star!

It didn't even occur to me to try doing it through the back end, but your suggestion made it very obvious.

It is the Worksheet.QueryTable.Connection property.

I am still curious to know if it can be done through the user interface, if you or anyone else has come across it. If not, it seems a bit of an oversight on MSs behalf, as I would not expect to tell users to go into the VBA editor if they want to change their connection.

Thanks again,
Danny...

 

[DannyLondon]

The Microsoft site officially acknowledges that the datasource for queries and pivot tables cannot be changed from the front end, and must be done through code.