ODBC dsn changing HOST problem

[Pablito9]

Hi,

I have the following problem;

I have a mdb database with linked ODBC tables in it that refer to a certain host, (HOST=server-name-1)
now I would like to change the HOST to the new server (HOST=server-name-2) that now holds the database , but I cannot find the way to do so.

I'm using Microsoft Office Access 2003 sp3.

Thanks,


Erik

 

[genomon]

As ar as I know you need to recreate the DSN and relink all the tables.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[maxhugen]

If it's a File DSN you could edit the text DSN file that was created.

Max Hugen
Australia

 

[JoeAtWork]

If you use a DSN-less connection, you could use the ChangeSQLFunction below. An example of a DSN-less connection string for SQL Server might be:

ODBC;Driver={SQL Native Client};Server=MyServer\sql2005;Database=MyDB;Uid=MyUID;Pwd=MySecretPassword;

The function below can be used to change the connection string of all non-system tables (if your database is not SQL Server, you will need to take out the part where it checks for a "dbo." prefix):

'Created:       17-July-2007
'Version:       2.0.2
'Purpose:       Changes the ODBC connection string for all SQL Server linked tables
Public Sub ChangeSQLConnection(NewConnection As String)
    Dim dbs As DAO.Database
    Dim tdf As TableDef
    
    Set dbs = CurrentDb
    
    For Each tdf In dbs.TableDefs
        'Only change tables linked to SQL server
        If Left(tdf.SourceTableName, 4) = "dbo." And Left(tdf.Name, 4) <> "MSys" And _
         tdf.Connect <> "" Then
            tdf.Connect = NewConnection
            tdf.RefreshLink
        End If
    Next
    
    Set tdf = Nothing
    Set dbs = Nothing
    
    MsgBox "Finished Relinking!", vbInformation, "Finished"
End Sub