DSN auto update?

[Guest]

We have several hundred mdb databases, created with Access 2000, 2002, and
2003. They all use system DSNs to gain access to a central SQL Server, via
both linked tables and pass through queries. The system DSNs use SQL Server
authentication. The database that they access is being moved to another SQL
Server, and once there, we want all authentication to be via Windows
Authentication. We are trying to make the transition as seamless as possible.
So far, this is (was?) our plan:

1. Create a file DSN on a network share, specifying trusted connection.
2. Convert all mdb's to use this instead of the local system DSN.
3. When the SQL Server database gets moved, modify the file DSN to reflect
the new servername.

This doesn't work because Access caches the DSN information, and apparently
only through the process of re-linking (very manual process) will the new DSN
information be used.

How can we make this move more seamless?

Thanks
Vern Rabe

 

[Douglas J. Steele]

Why not try DSN-less? See
http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.

 

[Guest]

I don't see how going DSN-less would help make the transition more seamless.
In the referenced document it states "The code below needs to be run any time
you want to change the server and/or database to which the tables are
linked". I'd have to run the code for each of the several hundred apps
coincident with the server change. I was hoping for some way to automatically
purge the cached DSN information, and automatically relink existing tables
using the new DSN. Oh, and I didn't mention in the original post that
probably 10-20% of the applications were written by developers (capable of
creating DSN-less VB code), and the remainder were created by MS Office users
who know how to link tables using their DSN definition, but not how to create
code.

Maybe a more succinct question is: What is the easiest way (fewest
keystrokes) to force an mdb appliction to purge the cached DSN connection
information and relink all existing linked tables?

Thanks
Vern Rabe

 

[Douglas J Steele]

You talked that you needed to touch every application in order to change it
to use the DSN on the server. That's why I thought that if you had to change
the applications in any case, removing the dependency on the DSN might be an
idea.

Since I typically don't use DSNs, I'm afraid I don't have an answer for your
specific question.

 

[Guest]

Yes, I have to touch them all, but we've got a few weeks (months?) before we
change servers, and I can more leisurely change the current DSN definition to
use the new file DSN. Then, on some weekend, we'll move the SQL Server
database, and on Monday morning, all mdb's should be up and running, using
the new SQL Server.

But after looking you your code more, I'm wondering if I couldn't modify it
to loop through all linked tables, and simply force a relink. If so, I could
create a module with said code in each mdb, run the code, then drop the
module. Not great, but better than manually relinking every table. Am I
wasting my time? Or maybe leave the module in and have it run on
initialization?

Thanks again for you ideas & help
Vern Rabe

 

[Douglas J Steele]

You can leave the module in, just in case you ever need to relink in the
future.

I typically have an INI file that accompanies the MDB file. In there, I
store the details for the current data source. Everytime the database opens,
it reads the INI file and compares what's there to the current linkage. If
they're different, it relinks the tables.

 

[Tony Toews]

But after looking you your code more, I'm wondering if I couldn't modify it
to loop through all linked tables, and simply force a relink. If so, I could
create a module with said code in each mdb, run the code, then drop the
module. Not great, but better than manually relinking every table. Am I
wasting my time? Or maybe leave the module in and have it run on
initialization?

Absolutely. I'd definitely agree with this approach. And I've done
exactly this in the past. And I agree with Doug's approach of
storing this connection data in an INI file. (Or files as required.)
Preferably read only except to you and the IT group.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm