Resetting Linked Tables DSN connection

[Wayne]

I have several access 2003 databases. We're using a file-dsn for connecting
to the sql server for the linked tables within it (so we can update all users
in one spot when we move from production to failover servers (which we do a
week-long quarterly test)).

However, the linked tables keep the connection information for when the
tables were added. I'm trying to automate this.

I was trying to put the following code in the form_open event:
Private Sub Form_Open(Cancel As Integer)
Dim tdfCurrent As DAO.TableDef
For Each tdfCurrent In DBEngine.Workspaces(0).Databases(0).TableDefs
tdfCurrent.RefreshLink
Next

This is generating the following error on the tdfCurrent.RefreshLink line:
Run-time error '3151':
ODBC--connection to <name> failed.

Can you help me get this working?
BTW, I'm a SQL DBA, not an Access programmer, so don't assume I know
anything about Access. I might need a little bit of help understanding.

Thanks,
Wayne

 

[Wayne]

Okay, I see what I'm missing... I need to set the connect property first.
How would I go about getting the connection string information from the DSN
file?

 

[Wayne Sheffield]

Terry,
Thank you very much for your help.
FYI, this is the code that worked:

Dim tdfCurrent As DAO.TableDef
For Each tdfCurrent In DBEngine.Workspaces(0).Databases(0).TableDefs
if (InStr(tdfCurrent.Connect, "DRIVER=SQL") > 0 ) or _
(InStr(tdfCurrent.Connect, "DSN=<name of my dsn>" then
tdfCurrent.Connect = "ODBC;FileDSN=<name of my dsn>"
tdfCurrent.RefreshLink
Next

It appears that this loop actually finds the original DSN entry (when
looking for "DSN="), and changing just this doesn't change any of the linked
tables connect string. If I go ahead and look for linked tables that have
any of the SQL Drivers, then this updates the linked table to use that
FileDSN. And, the next time I run this, I see that it is still set to the
FileDSN. I guess once it's set, it will always look at the set FileDSN
connection. But, since the users can always add more, and then they'll be
b****** when we roll over to our backup servers and their stuff doesn't
work, I'll just keep all of this in there to reset them every time.