Fix connection string for old ODBC linked table

[Terry]

I have an old Access database with tables linked through
ODBC. The database access is linked to has moved. How do I
modify the connection string information for the linked
tables?

I found the function below on MS knowledge base. Calling
the function with one of my table names I stopped once tbl
was set and viewed tbl.connect in VBA immediate window:

? TBL.Connect
ODBC;DSN=D122;SRVR=d122.knu.mycompany.com;UID=terry;DATABAS
E=

I think all I need to do is change d122 to another DSN.
How would I do that (permanently)?

Thanks,

Terry


Function DoesTblExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As DAO.Database, tbl As DAO.TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function

 

[jmonty]

I'm not sure this is exactly what you are looking for, but
you might try this:
Click [Start] > Setting > Control Panel > Administrative
Tools > Data Sources (ODBC)
Click on the System DSN tab and scroll down the list to
see if a DSN has already been created for the new database
location. If not, you will have to create a new one. (If
you don't know how ask your DBA or Admin to help) Once
that is completed, close that all out. Open your Access db
and from the Tools menu select Add-ins then Linked Table
Manager. Check all the tables you need and make sure to
check the box at the bottom that says "Always prompt for a
new location" then click OK. On the Machine Data Source
tab scroll down to the new DSN then OK. Once you type in
the correct passwords (again verify them with your DBA),
it will refresh the linked tables to the new location.
Hope this helps.