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

 

[Terry Kreft]

Set the Connect property first and then refreshlink

Something like:-

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim tdfCurrent As DAO.TableDef

Set db = CurrentDb

For Each tdfCurrent In db.TableDefs
With tdfCurrent
If Len(.Connect) > 0 Then
.Connect = "set your connect string here"
.RefreshLink
End If
End With
Next
End Sub

 

[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?

 

[Terry Kreft]

"set your connect string here" would be of the form

"ODBC;FileDSN=C:\Program Files\Common Files\ODBC\Data
Sources\Testee.dsn"

Obviously replace C:\Program Files\Common Files\ODBC\Data Sources\Testee.dsn
with the path to your DSN file.

If your DSN file is in the defaultDSN folder then you don't need the full
path and
"ODBC;FileDSN=Testee.dsn"

.... will do

 

[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.