Automate ODBC DSN Changes

[deanjenn]

We currently have System DSN ODBC data sources on Win 2K Pro and XP Pro clients that point to our MS SQL databases which reside on a single SQL server. We will be migrating SQL to a new server and I was wondering if there was a way to automate the server name change on each of the clients without manually going to Control Panel > Administrative Tools > Data Sources (ODBC) > System (Tab) and editing each entry manually.

Thanks for your thoughts/ideas!

 

[PCLewis]

Yes there is. I happen to have some code that will "aggressively" relink any tables that the application needs. I am forced into this because I look after a legacy application where I am not given leeway to replace ODBC, or replace the reliance on linked tables. I have three DSN's using the same application (dev, test and prod) so my function is used at development time before I release the new code, to ensure that all of my tables are linked to the correct instance.

You could use the code to achieve what you are asking. If the attached code helps then cool

Public Function LinkOracleTable(sLocalName As String, sForeignName As String, Optional bShowErrors As Boolean = False) As Boolean
  Dim sSQL As String
  Dim oRS As ADODB.Recordset
  Dim bAlreadyLinked As Boolean
  Dim bCreateLink As Boolean
  Dim bDeleteLink As Boolean
  Dim msg As String
  Dim sTemp As String
  
  If sLocalName = "" Then sLocalName = sForeignName
  
  sSQL = "SELECT Name, Type, Database, ForeignName from MSysObjects WHERE Name='" & sLocalName & "'; "
  Set oRS = New ADODB.Recordset
  oRS.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
  If Not oRS.EOF Then
    If oRS.Fields("Type") = 4 Then
        bDeleteLink = True
        bCreateLink = True
    Else
      ' name exists but is not attached
      If bShowErrors Then
        MsgBox "Failed to link to table '" & sForeignName & "' as " & sLocalName & vbCrLf & "This is required to ensure the most up to date database table is linked", vbCritical, "Delete Link"
      Else
        Debug.Print "Failed to link to table '" & sForeignName & "' as " & sLocalName
      End If
    End If
  Else
    bCreateLink = True
  End If
          
  If bDeleteLink Then
    ' linked but to a different name
    Err.Number = 0
    If GetObjectType(sLocalName) = 4 Then DoCmd.DeleteObject acTable, sLocalName
    If Err.Number <> 0 Then
      If bShowErrors Then
        MsgBox "Failed to delete existing table '" & sLocalName & "'" & vbCrLf & "This is required to ensure the most up to date database table is linked", vbCritical, "Delete Link"
      Else
        Debug.Print "Failed to delete existing table '" & sLocalName & "'"
      End If
      GoTo exit_linkoracletable
    Else
      Debug.Print "Deleted existing link '" & sLocalName & "'"
    End If
  End If

  If bCreateLink Then
    ' now create link to table
    
    'On Error Resume Next
  sTemp = gsConnectString

    DoCmd.TransferDatabase acLink, "ODBC Database", sTemp, acTable, sForeignName, sLocalName
    
    If Not GetObjectType(sLocalName) = 4 Then
      If bShowErrors Then
        MsgBox "Failed to link to table '" & sForeignName & "' as '" & sLocalName & "'" & vbCrLf & "This is required to ensure the most up to date database table is linked", vbCritical, "Delete Link"
      Else
        Debug.Print "Failed to link to table '" & sForeignName & "' as '" & sLocalName & "'"
      End If
      GoTo exit_linkoracletable
    Else
      Debug.Print "Linked to table '" & sForeignName & "' as '" & sLocalName & "'"
    End If
  End If
  
  LinkOracleTable = True
  
exit_linkoracletable:
  
  Exit Function
err_linkoracletable:
  GoTo exit_linkoracletable
End Function

Public Sub RelinkAllOracle()
  Dim bRes As Boolean
  bRes = True
  DoCmd.SetWarnings False
  bRes = LinkOracleTable("table1", "TEST.table1")
  bRes = bRes And LinkOracleTable("table2", "TEST.table2")
  bRes = bRes And LinkOracleTable("table3", "TEST.table3")
End Sub

 

[ArtieChoke]

The best way to handle it is to create a file DSN if your app supports that. Otherwise, you will need to extract the entries from the registry, make the change in the file and push that out to each machine in a login script. There might be other ways to remotely update in the NT resource kit. It ain't pretty, that's fur sure!

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[PCLewis]

Oh.

On rereading the original post I can see I was not answering the question posed. Please ignore me...you saw nothing