Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Replacing connection string info (only the server name) (ODBC) through VBA

    I have just converted a large and complex ADP file to compatible 2013 accdb format.

    All of the testing was done on test database of identical table names, identical DB name but a different server - so in essence a different DSN

    I wrote the code that now modifies the connection string for the Passthrough queries using the QueryDef and a replace function to replace the sever name with the new server name

    But how do I write the code to change the connection strings of the tables if they are already using the older dsn can I just point to a new dsn of the same name (the objects within the database are identical) that uses a different server name using the link table manager and all the connection strings will be modified without me having to go through that process of clicking save password and reselecting the primary key like I had to the first time I did this on the test DB. Over 500 tables - do not want to do this again
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use this:
    Code:
    Sub RelinkTables(ByVal NewDSN As String)
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb ' Can be: Set dbs = "<Path>\<Database>" if the tables are in another Access database (backend).
        For Each tdf In dbs.TableDefs
            With tdf
                If Len(.Connect) > 0 Then
                    .Connect = NewDSN
                    .RefreshLink
                End If
            End With
        Next tdf
        Set dbs = Nothing
    
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •