change DSN connections to DSN-Less conntections via VBA

[sparkbyte]

I need a way to catalogue all the linked tables in the Access Application then remove them and replace them with DSN-less linked tables.

I have a couple of functions that will delete all the ODBC linked tables and create DSNless connections.

What I am having problems figuring out how to do is to get the Linked table properties and store the DSN properties so that I can then recreate them as DSNless connections.

Public Sub DeleteODBCTableNames(Optional stLocalTableName As String)
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, i As Integer
Set dbs = CurrentDb

If Len(stLocalTableName) = 0 Then
    For i = dbs.TableDefs.Count - 1 To 0 Step -1
        Set tdf = dbs.TableDefs(i)
            If (tdf.Attributes And dbAttachedODBC) Then
            dbs.TableDefs.Delete (tdf.Name)
            End If
    Next i
Else
    dbs.TableDefs.Delete (stLocalTableName)
End If

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source & Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames
End Sub

Function connectToDababase(Optional m_connectionString As String) As String
'// This Function requries
'// Microsoft OLE DB Service Component 1.0 Type Library
'// Microsoft ActiveX Data Objects 2.7 Library

Dim dl As MSDASC.DataLinks
Dim cn As ADODB.Connection

Set dl = New MSDASC.DataLinks
Set cn = New ADODB.Connection



If IsMissing(m_connectionString) Then
    Set cn = dl.PromptNew
Else
    cn.ConnectionString = m_connectionString
    dl.PromptEdit cn
End If

connectToDababase = cn.ConnectionString
End Function

Thanks

John Fuhrman

 

[dhookom]

I just use code that resets the properties. I don't delete the link and then add them back in. You would need to supply the correct values for XXXX.

Function RelinkODBC()
    Dim td As DAO.TableDef
    Dim qd As DAO.QueryDef
    Dim db As DAO.Database
    Dim strConnect As String
    strConnect = "ODBC;Driver={SQL Server};Server=XXXX;Database=XXXX;Uid=XXXX;Pwd=XXXX"
    Set db = CurrentDb
    For Each td In db.TableDefs
        If Left(td.Name, 4) = "dbo_" Then
            If Left(td.Connect, 4) = "ODBC" Then
                Debug.Print td.Name
                td.Connect = strConnect
                td.Attributes = DB_ATTACHSAVEPWD
                td.RefreshLink
            End If
        End If
    Next
    For Each qd In db.QueryDefs
        If Left(qd.Connect, 4) = "ODBC" Then
            Debug.Print qd.Name
            qd.Connect = strConnect
        End If
    Next
    Set td = Nothing
    Set qd = Nothing
    Set db = Nothing
    
End Function

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

Pretty much what I use too, altho I use the Instr function to test for the database name first.

Public Function RefreshLinks()

Dim tdf As DAO.TableDef
Dim stconnect As String

For Each tdf In CurrentDb.TableDefs


If InStr(tdf.Connect, "DATABASE=GlobalObjects") Then
stconnect = "ODBC;DRIVER=SQL Server;SERVER=SQL\TRAVERSE;DATABASE=GlobalObjects;UID=xx;PWD=xxx;APP=Microsoft Data Access Components;WSID=TERMINAL"
tdf.Connect = stconnect
tdf.RefreshLink
End If




If InStr(tdf.Connect, "DATABASE=Line2_Production") Then
stconnect = "ODBC;DRIVER=SQL Server;SERVER=sql;DATABASE=Line2_Production;UID=xxx;PWD=xxx;APP=Microsoft Data Access Components;WSID=TERMINAL"
tdf.Connect = stconnect
tdf.RefreshLink

End If

'etc for each database on the SQL server


Next

Set tdf=nothing



End Function

 

[1DMF]

Same here , i use this function and then loop the table defs passing it the info.

Public Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function
    
AttachDSNLessTable_Err:

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & err.Description
End Function

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[sparkbyte]

1DMF, I am using the same function to create the DSNless linked table connections, but I have inherited about 70 Access applications that have multiple linked tables to a variety of SQL backends and would like to remove the existing connections and recreate them as DSNless.

The connections to the SQL server are done via NT Domain security so there are not any credentials stored in the DSNs at the workstations and will not be required in the new connections.

Idealy, I would also like to copy the connection information in a table on the SQL server.



Thanks

John Fuhrman

 

[1DMF]

Well as you are using Access , VBA and linked tables, the info is stored in access as to the connection data, I'm not sure how you'd get any info from the actual SQL table, or if the SQL tables store connection info, out of my knowledgebase, sorry!

There may be a 'sys' table you can look at, but I tend to steer clear of system tables in SQL, last thing you want to do is screw with them and cock up the SQL server!

I have a similar situation where we have multiple databases across multiple servers, the way I reconnect each table using DNSLess coonections to the correct server with the right connection string is 1stly ensuring that tables from different places use different schema prefixes as part of their name.

Then I can use that as a guide to correctly set the connection string like so...

Public Function SetLinkedTables()

    Dim db As Database
    Dim Cnct As String
    Dim tdf As TableDef
    Dim sCon As Variant
    Dim a As Integer
    Dim sVar As Variant
    Dim sName As String
    
    Dim sServer As String
    Dim sPWord As String
    
    
    On Error GoTo SLT_Err

    Set db = CurrentDb

    db.TableDefs.Refresh
  
    ' Loop Table Defs
    For Each tdf In db.TableDefs
        With tdf
            'split current string to find db data
            sCon = Split(.Connect, ";")
            For a = 0 To UBound(sCon)
                If sCon(a) <> "" Then
                    sVar = Split(sCon(a), "=")
                    If sVar(0) = "DATABASE" Then
                        Cnct = sVar(1)
                        If left(.Name, 4) = "dbo_" Then
                            sName = right(.Name, Len(.Name) - 4)
                            sServer = "Server IP, Port"
                            sPWord = "myPassword"
                        ElseIf left(.Name, 4) = "aff_" Then
                            sName = right(.Name, Len(.Name) - 4)
                            sServer = "Server IP, Port"
                            sPWord = "myPassword"
                        ElseIf left(.Name, 3) = "ar_" Then
                            sServer = "Server IP, Port"
                            sPWord = "myPassword"
                            sName = right(.Name, Len(.Name) - 3)
                        Else
                            sName = .Name
                            sServer = "Server IP, Port"
                            sPWord = "myPassword"
                        End If
                        ' create connection to table
                        Call AttachDSNLessTable(.Name, sName, sServer, Cnct, "sa", sPWord)
                    End If
                End If
            Next
        End With
    Next
                       
    db.TableDefs.Refresh

    Set tdf = Nothing
    Set db = Nothing
        
    MsgBox "Tables Re-Linked"

SLT_Exit:
   Exit Function

SLT_Err:
   MsgBox "Error in SetLinkedTables : " & err.Description
   Resume SLT_Exit

End Function

So this loops the table defs and then calls the other function using the correct attributes for the connection paramters.

If you only have the default instance running in SQL then you won't need to use port number just IP should be fine (I prefer it to server name).

Hope it helps.


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts