Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

Access VBA Automatically link to back-end issue

Hi. I am using the following code to refresh links to an Access backend that is in the same folder when my client first opens a file. If an error is caused by one of the tables not being there I don't want the link to be refreshed. At the moment the link seems to refresh even if there is an error

Function fRefreshLinks() As Boolean

    On Error GoTo EH
    Dim N As String
    N = LCase(CurrentProject.Name)
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            With tdf
                If .Connect Like ";DATABASE=*" Then
                      Debug.Print "Previous: " & .Connect    'Show me previous
                      If InStr(N, "accde") > 0 Then
                           .Connect = ";DATABASE=" & CurrentProject.Path & "/ArmsTracker Data.accde"
                          .Connect = ";DATABASE=" & CurrentProject.Path & "/ArmsTracker Data.accdb"
                      End If
                     Debug.Print "New: " & .Connect         'Show me new
                End If
            End With
        If InStr(N, "accde") > 0 Then
            MsgBox "Successful connection to " & CurrentProject.Path & "/ArmsTracker Data.accde"
            MsgBox "Successful connection to " & CurrentProject.Path & "/ArmsTracker Data.accdb"
        End If
        fRefreshLinks = True
    Exit Function
    MsgBox "DATA FILE RE-CONNECTION ERROR: " & Err.Description
    fRefreshLinks = False

End Function
Microsoft AccessVBA

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon