Thread: Dynamic change SQL ODBC data source for use in Excel 2007

Hi People

I have the following requirement. I have searched the forum and a few posts came close but I still could not find what I need.

I am not a programmer and my knowledge of macros goes about as far as recording them and linking them to a button.

I have created a sheet in Excel 2007.
In cell A1 I have a list of 3 server names. (Call tem server01/02/03)
In cell A2 I have a list of 7 databases. (Call them db01/02…07)

The report have a refresh button the user can click to refresh the data. (Calling the refresh all macro)

The names are user friendly names. In other words, AdminData would point to server svrmain and SalesData to svrdata01 (Get the idée?)
The same with the database.
The user would choose Sydney that point to database syddata and when choosing Adelaide it would point to addata etc

The actual selection in the server could be from a table(s) and/or view(s) and/or stored procedure(s) but it would stay constant per report.

So what we have so far is a user opening a specific report (of many) that is linked to , let’s say a table called table01. Depending on what the user enter in Cell A1 and A2 the user control from what server and database the contents of Table01 is returned from. If opening another report that report would be lined to a diff table or view etc)
(I use the word linked but the meaning is the table that would supply the data for the report.)

Second important thing (I think) is that we are looking at different SQL servers 2000, 2005, 2008.
At the moment I have an ODBC link setup for every server and every database depending on the native drivers I use.
I use the following naming convention for the ODBC names <servername>_<native client><database>
Server01_00datbase01..07 Sql2000 Using Native SQL drivers for it with the 7 databases
Server02_05datbase01..07 Sql2005 Using Native SQL drivers 9 for it with the 7 databases
Server03_10datbase01..07 Sql2008 Using Native SQL drivers 10 for it with the 7 databases

All the servers use the same username and password to access the data
ReadOnlyUser
Password

So currently looking at any 21 of the same reports (3 servers x 7 databases) the only difference is the connection string

What I was thinking of doing but have no idée how to do it was the following:

Based on what the user enter in A1 and A2 to dynamically change the connection string so that Excel point to the correct ODBC and/or update the connect string as required per the A1 A2 entry.

Obviously if there is better ways to do feel free to advise.

The part that have to stay constant is the user entering the server and database to be used and the user must have the control to decide when a refresh should happen
(Other parameters is passed but that is used in the select and does not form part of the source pointing)

I have tried to give as much info as I could. If something is short, please feel free to ask.

Please not the macro's must be 2007 compatable

Regards
Burger

I hate to reply to an old post, but I am wondering if you found a solution to this problem. I am currently trying to accomplish something very similar to what you have described.

Hi
No I have not. Still looking. I am close but not there. If I find something I wil post the solution