Excel Data Connections DSN resolution
I've noticed an Excel behaviour with data connections that I am looking for ways to sidestep. Say that you set up a new connection pointing to DSN1. You build a query, test it, love it and save it. Fine so far. Now, say that you then change the definition of DSN1 so that it points to a different server. I would have thought that Excel wouldn't care and would just build the ODBC connection when the query is executed. Wrong! I now see that Excel extracts vital info from the definition of DSN1, including the server address, when the data connection is set up. Then, if you change the DSN definition using Control Panel/Admin functions, Excel complains that they don't match. Well, of course they don't match! That's the entire point of the exercise.
So....what can I do? I have a requirement to build a spreadsheet and send it to various users, each of whom will have the same DSN defined *but* possibly pointing to different database servers. I do not want the users to have to fiddle with the data connections in the spreadsheet.
Porbably not good suggestion, but...
If there is not many users, try to export DSN from Registry and manually change server data in .reg file for each user. Then send .reg files to users, respectivelly. When they run files, DSN will be set up.
I'm afraid that won't work
I'm afraid that won't work since Excel has already resolved the DSN and saved the resolved address. e.g. If I just change the target DSN and try to rerun the same query, it fails since the new source DB doesn't match the one Excel saved when the connection was first built.
It's too smart for its own good!