Changing ODBC for multiple users

[David H.]

Our database has an Access 2007 front end and an MSSQL 2005 back end which we
need to move to a new server. I know I can easily log on as an admin to each
user's comptuer, go to Database Sources (ODBC), and modify the System DSN to
point to the new location.
But is there a faster way to update users' computers? I hate for them all
to be unable to do their work while I plod from workstation to workstation.
(All right, I will actualy plod virtually using remote control software, but
even so...)

 

[Douglas J. Steele]

I find the easiest way is to go DSN-less, and have a routine in the
front-end that will correctly relink the tables when necessary.

I show an example at http://www.accessmvp.com/DJSteele/DSNLessLinks.html Of
course, by the time you get the additional code tested, and the new
front-end distributed to everyone, you could probably have updated all their
DSNs...

There's a link at the page above to code to programmatically create DSNs.
Recognize that a DSN is simply a bunch of entries in the registry (System
DSNs are under HKLM\Software\ODBC\ODBC.INI, User DSNs are under
HKCU\Software\ODBC\ODBC.INI). You could just run a .reg file to update each
of the users...