Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    30

    Unanswered: Code to refresh links

    I'm looking for code to refresh links at startup. Nothing fancy all tables in same backend. Found code in an old solutions.mdb but won't run in access 2003.

    Private 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

    Any help would be greatly appreciated.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Code:
    DoCmd.RunCommand acCmdLinkedTableManager
    This brings up the Linked Table Manager dialog box, and you must select files as necessary.

    But something confuses me. Since links are dynamic connections, meaning that all updates happen without action on the part of the user, why do you want to refresh the links at startup? I use linked files all over my databases and haven't needed to update them once. And the links are in some cases over two years old!

    Hope this is helpful,

    Sam

  3. #3
    Join Date
    Jul 2005
    Posts
    30
    Sam:
    Thanks for the reply. I was able to get the code to run by refereceing the DAO library in vb. I had the same thought as you about the linked tables.
    My real problem is that I upgraded to access 2003 and found that I am now unable to edit linked tables. Update queries no longer work. Microsoft suggests editing the excel sheets. I may just go back to 2002 where everything worked.
    Last edited by darejamr; 07-11-06 at 13:22.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You are right. I cannot edit data in files that I link to using an ODBC connection. The connection is as slow as molasses even without the feature that would allow me to edit the data. That slows the connection even more. And in my case I really don't need it anyway.

    I don't know if I can edit data in files that I link to using Excel and Microsoft Query. While the connection is considerably faster than ODBC, I don't need to edit that data either.

    Good luck,

    Sam

Posting Permissions

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