Changing ODBC Data Source configuration doesn't force Excel query refresh to new database. How to work around?

Greetings Experts!

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!
monkeybiz12345Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slubekCommented:
I'm using approach similiar to what you want to achieve: user enters query parameter into specified cell an hits macro-starting button.

My macro modifies ActiveWorkbook.Connections("ConnectionName").ODBCConnection.CommandText to
SELECT * from functionname(parameter)

Open in new window


Try seting .Connection to Your new ConnectionString like this:
Dim DBName as String="DB2"
With ActiveWorkbook.Connections("ConnectionName").ODBCConnection
    .BackgroundQuery = True
    .Connection= _
"ODBC;DRIVER=SQL Server;SERVER=SERVERNAME;UID=USERNAME;Trusted_Connection=Yes;APP=Microsoft Office 2003;DATABASE=" & DBName
    .Refresh
End With

Open in new window

0
monkeybiz12345Author Commented:
Thank you for your response.  However, I was not looking for any type of code or macro.
0
monkeybiz12345Author Commented:
I want to be able to just change the SQL database that my ODBC data source points to and have all of my Excel queries just work.    I was looking for either something that I was missing that would give me what I wanted or confirmation of that's how it works.

In any case, I no longer need responses to this question.  I ended up manually editing the Excel queries by changing the database name in the connection string and SQL command text in Connection Properties.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hnasrCommented:
Can you help me to recreate the problem in Excell?

Compare with this in Access linking remote MySQL database.
Setting the connect string for the linked table defined as tdf with the new connection string replacing the database name "trial", with the other database name "trial-copy".

            tdf.Connect = Replace(tdf.Connect, "trial", "trial-copy")
            tdf.RefreshLink

Open in new window

0
monkeybiz12345Author Commented:
It still looks like neither my original question, nor my attempt to clarify it, was clear.  I specifically asked for a process that does not include any type of code or macro.  

The answer to my question appears to be that I cannot simply change the SQL database that my ODBC data source points to and have all of my Excel queries just work without editing them.  It doesn't work that way.  One must with either manually edit the connection string and SQL command, as I ended up doing, or use a macro or procedure to change it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
monkeybiz12345Author Commented:
It still looks like neither my original question, nor my attempt to clarify it, was clear.  I specifically asked for a process that does not include any type of code or macro.  

The answer to my question appears to be that I cannot simply change the SQL database that my ODBC data source points to and have all of my Excel queries just work without editing them.  It doesn't work that way.  One must with either manually edit the connection string and SQL command, as I ended up doing, or use a macro or procedure to change it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.