[AccessD] Updating Connect string for SQL Linked tables

MartyConnelly martyconnelly at shaw.ca
Fri Nov 4 13:20:33 CST 2005


Here is some code, to look at that does work. Tribble's BackEnd Relinker 
(alink21)
Available under menu  "useful files", you will have to register.
http://www.colbyconsulting.com/
look at the function fChangeLink
There is a lot of extraneous code to do with file dialog to get changed 
backend names.

rusty.hammond at cpiqpc.com wrote:

>I have a form (A97) that I designed to be able to change the parameters of a
>connect string for any or all linked SQL tables in the current database that
>the form resides in.  It seems to work once or twice on a database but then
>quits working.  By quits working, I mean it goes through the code, looks
>like it's working, but when you check the connect string (either through the
>properties of the table, or looking at the msysobjects table), the connect
>string is still the old one and the changes are not reflected in the
>connection information on the SQL server.
>
>I'm posting the code used to update the connection strings.  Any ideas would
>be greatly appreciated.
>
>Private Sub ChangeSQLODBCTables(strName As String, strOption As String,
>strTo As String)
>'***************************************************************************
>********************
>'*** This subroutine will refresh any tables that are linked to the Access
>database via ODBC ***
>'*** and will re-create the primary pseudo-index on the newly re-linked
>table if necessary   ***
>'*** Created by RustyH - 3/5/2003
>***
>'***************************************************************************
>********************
>    Dim db As Database
>    Dim tdf As TableDef
>    Dim tdfNew As TableDef
>    Dim strConnect As String
>    Dim strSourceTbl As String
>    Dim strIndex As String
>    Dim idx As Index
>    Dim fld As Field
>    Dim strFields As String
>    Dim X As Integer
>    Dim strSQL As String
>    Dim fNewHasPrimary As Boolean
>    Dim fOldHadPrimary
>    Dim strStatus As String
>       
>    Set db = CurrentDb
>    
>    Set tdf = db.TableDefs(strName)
>    strConnect = tdf.Connect
>    fOldHadPrimary = False
>    'Test if the connect string starts with ODBC - Linked access tables
>don't show a link type
>    'and local tables have an empty connect string ("")
>    
>    If Left(tdf.Connect, 5) = "ODBC;" Then
>    
>        strSourceTbl = tdf.SourceTableName
>        strStatus = SysCmd(acSysCmdSetStatus, strName)
>        For Each idx In tdf.Indexes
>            'Search for a primary index on the table
>            If idx.Primary = True Then
>                fOldHadPrimary = True
>                'Get the primary index name
>                strIndex = idx.Name
>                strFields = ""
>                X = 0
>                'Get the field list that makes up the primary index
>                For Each fld In idx.Fields
>                    If X = 0 Then
>                        strFields = fld.Name
>                    Else
>                        strFields = strFields & "," & fld.Name
>                    End If
>                    X = X + 1
>                Next fld
>            End If
>        Next idx
>        'Refresh the link to the table
>        If strTo = "" Then
>            strConnect = ReplaceString_SQLTableOption(strConnect, ";" &
>strOption & "=", "", False)
>        Else
>            strConnect = ReplaceString_SQLTableOption(strConnect, ";" &
>strOption & "=", ";" & strOption & "=" & strTo & ";", False)
>        End If
>        If strConnect = "NO" Then
>            Stop
>            'Me.StatusMsg = Me.StatusMsg & vbCrLf & vbCrLf & strName & " is
>not linked to the " & strFrom & " server"
>        Else
>            MsgBox "New connect: " & strConnect, vbOKOnly
>            
>            'tdf.Connect = strConnect
>            'tdf.RefreshLink
>            
>            db.TableDefs.Delete strName
>            
>            Set tdfNew = db.CreateTableDef(strName)
>            tdfNew.SourceTableName = strSourceTbl
>            tdfNew.Connect = strConnect
>            db.TableDefs.Append tdfNew
>            tdfNew.RefreshLink
>            'db.TableDefs.Refresh
>            
>            fNewHasPrimary = False
>            For Each idx In tdfNew.Indexes
>                'Determine if the newly refreshed table already has a
>primary index
>                If idx.Primary = True Then
>                    fNewHasPrimary = True
>                End If
>            Next idx
>            'If the newly refreshed linked ODBC table does NOT have a
>primary index but it did before
>            'the link was refreshed, then create a primary pseudo-index
>            If fNewHasPrimary = False And fOldHadPrimary = True Then
>                strSQL = "Create unique index " & strIndex & " on " &
>tdfNew.Name & "(" & strFields & ")"
>                DoCmd.RunSQL strSQL
>            End If
>            Me.StatusMsg = Me.StatusMsg & vbCrLf & strName & " done"
>        End If
>    End If
>    
>    db.Close
>    Set db = Nothing
>    strStatus = SysCmd(acSysCmdClearStatus)
>    
>End Sub
>
>
>The following function is called by the main sub to create the new connect
>string:
>
>
>Private Function ReplaceString_SQLTableOption(strTextIn As String, strFind
>As String, strReplace As String, fCaseSensitive As Boolean) As String
>    ' Comments   : replaces a substring in a string with another
>    ' Parameters : strTextIn - string to work on
>    '              strFind - string to find
>    '              strReplace - string to replace with
>    '              fCaseSensitive - True for case sensitive search, False
>for case-insensitive search
>    ' Returns    : modified string
>    '
>    Dim strTmp As String
>    Dim intPos As Integer
>    Dim intLen As Integer
>    Dim intCaseSensitive As Integer
>    
>    intCaseSensitive = IIf(fCaseSensitive, 2, 1)
>    
>    strTmp = strTextIn
>    intPos = InStr(1, strTmp, strFind, intCaseSensitive)
>    intLen = InStr(intPos + 1, strTmp, ";", intCaseSensitive) - intPos + 1
>    If intPos > 0 Then
>      Do While intPos > 0
>        If strReplace <> "" Then
>            strTmp = Left$(strTmp, intPos - 1) & strReplace & Mid$(strTmp,
>intPos + intLen)
>        Else
>            strTmp = Left$(strTmp, intPos - 1) & Mid$(strTmp, intPos +
>intLen - 1)
>        End If
>        intPos = InStr(intPos + intLen, strTmp, strFind, intCaseSensitive)
>      Loop
>    Else
>      strTmp = "NO"
>    End If
>        
>    If Right(strTmp, 1) = ";" Then strTmp = Left(strTmp, Len(strTmp) - 1)
>        
>    ReplaceString_SQLTableOption = strTmp
>  
>End Function
>
>
>TIA!
>
>Rusty Hammond
>rusty.hammond at cpiqpc.com
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the AccessD mailing list