Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Change Linked Tables source (2003 SP3)

    Edited by HansV to provide link to post. It's very simple: select the post number including the square brackets, i.e. <!t>[Post: 249,646]<!/t>. Press Ctrl+C to copy, then press Ctrl+V in your new post or reply to paste. The Lounge software will automatically convert it to a link when you click Post It.

    I found a post that almost addresses what I am trying to do here - <post#=249,646>post 249,646</post#>.

    I have a DB where I have local & network copies of the backend tables. These tables are in more then 1 database, but are all in the same path.

    What I want to do is create or use a module that is like the ones Hans shows in the post with the function RefreshLinks but would like the module to change the path to the supplied paramater for all linked tables regardless of which database they are in.

    Here is a copy of the code in that post:

    Public Function RefreshLinks(strFileName As String) As Boolean
    ' Refresh links to the supplied database. Return True if successful.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    tdf.Connect = ";DATABASE=" & strFileName
    Err = 0
    On Error Resume Next
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    RefreshLinks = False
    Exit Function
    End If
    End If
    Next tdf

    RefreshLinks = True ' Relinking complete.
    End Function


    I just don't understand enough about the VBA to just change the path or connection string.

    Thanks.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Linked Tables source (2003 SP3)

    Try this version:
    <code>
    Public Function RefreshLinks(strOldPath As String, strNewPath As String) As Boolean
    ' Update path of linked tables. Return True if successful.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    tdf.Connect = Replace(tdf.Connect, strOldPath, strNewPath)
    Err = 0
    On Error Resume Next
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    RefreshLinks = False
    Exit Function
    End If
    End If
    Next tdf

    RefreshLinks = True ' Relinking complete.
    End Function
    </code>
    Use it like this:
    <code>
    If RefreshLinks("C:Access", "ServerShareFolder") = True Then
    MsgBox "Tables relinked successfully!", vbInformation
    Else
    MsgBox "Relinking failed!", vbCritical
    End If
    </code>
    Substitute the appropriate paths.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change Linked Tables source (2003 SP3)

    Hans, thank you for the example. If I also have some ODBC connections (which I failed to mention), would this cause the code the fail?

    If I use the Immediate window with your code:
    ? RefreshLinks("Cocuments and SettingsHANDLE.GLOBALMy DocumentsAccess2k", "ServerNameShareName")

    I am still getting False.

    There is not a folder name in my data share. All of the files are in the root of the data share.
    If I look in MSysObjects in the Database Field, this is how the few network paths are currently listed ServerNameShareNameDatabase.mdb (and the same goes for the local files).
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Linked Tables source (2003 SP3)

    Yes, non-Access links would cause the code to fail. Does this work better? I have no way to test it myself.
    <code>
    Public Function RefreshLinks(strOldPath As String, strNewPath As String) As Boolean
    ' Update path of linked tables. Return True if successful.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    If Not Left(tdf.Connect, 4) = "ODBC" Then
    tdf.Connect = Replace(tdf.Connect, strOldPath, strNewPath)
    Err = 0
    On Error Resume Next
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    RefreshLinks = False
    Exit Function
    End If
    End If
    End If
    Next tdf

    RefreshLinks = True ' Relinking complete.
    End Function</code>

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change Linked Tables source (2003 SP3)

    Yes this is working for me now.

    I made a few slight changes to better suite the application & to continue changing the links even if there are 1 or more links that can't be updated. I also added a msgbox to display if all were refreshed or list those that could not be:

    Public Function RefreshLinks(strOldPath As String, strNewPath As String) As String

    ' Update path of linked tables. Return Results in a MsgBox.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    ' Loop through all tables in the database.

    Results = ""
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then

    If Not Left(tdf.Connect, 4) = "ODBC" Then

    tdf.Connect = Replace(tdf.Connect, strOldPath, strNewPath)

    Err = 0
    On Error Resume Next
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    Results = Results & tdf.Name & vbCrLf

    'Exit Function
    End If
    End If
    End If
    Next tdf

    If Results = "" Then
    Results = "All Completed"
    Else
    Results = "Not Refreshed" & vbCrLf & Results
    End If

    RefreshLinks = Results ' Relinking complete.
    MsgBox Results, vbInformation
    End Function



    Thank you very much for your code examples & help (again),

    John
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

Posting Permissions

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