Use vba to refresh table links (1 Viewer)

[ASM2792]

Hello,

I have designed a system that is used to track customer activity and log calls to a department. The front end and back end database are written in access. This system is due to go to the USA division of the company i work for.

The front end needs to automatically refresh the tables and if the backend database has moved (which it will when i send it to the US) then the code will look at a text file which will store the path to the new database location.

I have been looking at the code on the link below, however i do not fully understand how this code works in order to alter it to what I need.

("http:/ /access.mvps .org/access/tables/tbl0009 .htm")

 

[Uncle Gizmo]

("http:/ /access.mvps .org/access/tables/tbl0009 .htm")

 

[jdlc]

hi, this is the code i used in refreshing my link.

Function RefreshTableLinks() As String
On Error GoTo ErrHandle
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strCon As String
Dim strBackEnd As String
Dim strMsg As String
Dim intErrorCount As Integer
Set db = CurrentDb
For Each tdf In db.TableDefs
    If Mid(UCase(Trim(tdf.Name)), 1, 4) <> "MSYS" Then
        Forms!frmStartUp.Label23.Caption = "Refreshing the link of Table " & tdf.Name & ", please wait...."
        DoEvents
        SleepVBA (1)
        If Left(tdf.Connect, 10) = ";DATABASE=" Then
            strCon = Nz(tdf.Connect, "")
            strBackEnd = Right(strCon, (Len(strCon) - (InStrRev(strCon, "") - 1)))
            If Len(strBackEnd & "") > 0 Then
                RefreshTableLinks = strBackEnd
                Set tdf = db.TableDefs(tdf.Name)
                tdf.Connect = ";DATABASE=" & CurrentProject.Path & strBackEnd
                tdf.RefreshLink
            Else
                intErrorCount = intErrorCount + 1
                strMsg = strMsg & "Error getting back-end database name." & vbNewLine
                strMsg = strMsg & "Table Name: " & tdf.Name & vbNewLine
                strMsg = strMsg & "Connect = " & strCon & vbNewLine
            End If
        End If
    End If
Next tdf
ExitHere:
    On Error Resume Next
    
    If intErrorCount > 0 Then
        strMsg = "There were errors refreshing the table links: " _
        & vbNewLine & strMsg & "In Procedure RefreshTableLinks"
        RefreshTableLinks = strMsg
    End If
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
ErrHandle:
    intErrorCount = intErrorCount + 1
    strMsg = strMsg & "Error " & Err.Number & " " & Err.Description
    strMsg = strMsg & vbNewLine & "Table Name: " & tdf.Name & vbNewLine
    strMsg = strMsg & "Connect = " & strCon & vbNewLine
    Resume ExitHere
End Function

 

[ions]

Try the free JStreet Table Re-Linker.

http://www.jstreettech.com/downloads.aspx

Try the J Street Access Relinker version 1 that is not in Beta.