I'm having trouble wrapping my head around the use of ODBC Data Source in Excel queries and what happens if the Data Source configuration changes after queries have been written. I always thought that the ODBC data source configuration was a kind of go between - that I point my query to the data source, the data source points to the database, and the thing to maintain is the data source config.
For example, I have an ODBC data source defined on my machine that points to a SQL database called DB1. When I create a query in Excel using the MS Query wizard (Select Data, From Other Sources, From MS Query) I select my Data Source which points to a SQL database called DB1 and the table and fields I want in my query. If the name of my SQL database changes to DB2 (assume table and field names are the same), I want to be able to change my ODBC Data Source configuration to now point to DB2, refresh the data in my query, and have Excel pull data from the database my Data Source is now pointing to, DB2.
All the testing I've done so far suggests that it doesn't work this way. I can go into Connection Properties and manually edit the database name in the connection string and SQL command text.
Is there another way? Specifically, is there a way for users who wouldn't be comfortable editing the connection string and command text to avoid having to rewrite tons of locally stored queries that are now pointing to DB2?
Please note that I'm not looking for a solution that involves code. I'm looking for a procedure that can be managed by average users in their own queries.
Thanks, in advance, for your help!