Managing ODBC connections

[Danny]

We have a network of clients accessing excel spreadsheets on a Windows 2003
Server. The speadsheets point at data on SQL Server 2005. Excel installations
on the clients vary from Excel 2000 upto Excel 2007. On each client we create
an ODBC connection using System DSN. This has proved to be a pain to manage.
We are now moving the files to a new server therefore all the ODBC
connections need changing and so do the reports. Is there are better way to
manage these ODBC connections?

Thanks
Danny

 

[Conan Kelly]

Danny,

I don't have a whole lot of experience in this, but you ODBC connections
should be files stored in a folder somewhere. Maybe you can move all the
connections to a network share/folder that everyone can get to.

When I do a pivot table in XL > External Data > Get Data..., I get the
"Choose Data Source" dialog box. When I click the "Options..." button, I
get a list of folders that it is searching for data sources and an option to
add other folders. The 2 that are listed are:

c:\Program Files\Common Files\ODBC\Data Sources\
c:\Documents and Settings\[*** USER ID ***]\Application
Data\Microsoft\.....
(sorry, can't see the whole path on that last one, maybe someone else
can finish it)

Create data sources in a folder on a server somewhere (or copy them to that
folder after creating them on the first client) and add that network folder
to the list of folders to search for data sources.

HTH,

Conan