Change SystemDSN to FileDSN for 100's of MS Access and Excel Files

Question

A particular team in my office uses 100's of MS Access Models. They all connect with the database using SystemDSN, the name of the SystemDSN is hardcoded in the MS Access Models.

My team mate suggests that we use a login script to ensure that the SystemDSN is configured for all users who belong to that team (so, if a new joinee is part of the team, he gets the DSN as well rather than call IT).

I am suggesting that we use FileDSN, store the FileDSN on a network share path and be done with. The advantages being the following

1. If I were to migrate by database to a new server, I need to just update the FileDSN at one location and all done.

2. I can access the MS Access Model even via Citrix where I may not be able to have SystemDSN as such.

The problem, as my team mate suggests, is

1. There are over 100 MS AccessModels, so, how do you go about changing the hardcoded values of SystemDSN? He claims that it is not worth the effort.

Surely, there should be a programming concept for us to make the change at one go.

While I continue to rack my brain for a solution, if someone out there already has one do let us all know!

Answer 1

Use DSNLess connections instead, so that the client workstations don't need any setup, nor any accessible file-based DSN definition. I use Doug Steele's code to convert DSN connect strings to DSN-less connect strings:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

Answer 2

If it's only 100s (not thousands), then you will probably take less time changing them than you would writing a login script.

If you go the DSN-less route (see David's answer), here is another valuable resource: http://www.connectionstrings.com/