Programatically amend an ODBC Connection

[Rod Behr]

An ODBC connection on client machines points to a SQL Server (Server1). An
Access database, running on the client, stores its data on this SQL Server.
Clients have the ability to update their clients whenever new versions are
released (a simple batch file).

I want to move the SQL database to another server (Server2).

It it possible to write code from within Access that checks for whether a
named ODBC System DSN point to Server1 and, if so, amends the system DSN to
point to Server2?

 

[Douglas J. Steele]

There's code in http://www.mvps.org/access/tables/tbl0010.htm at "The Access
Web" that shows how to change a linkage that uses a DSN (albeit an Oracle
example). You can also the code from
http://www.accessmvp.com/DJSteele/DSNLessLinks.html if you'd prefer not
bothering having to set the DSNs on each machine.

 

[Rod Behr]

Thanks Douglas. I have not hit error "Could not find installable ISAM. (Error
3170)", but this appears to be a separate issue and I have raised it
separately on this forum.