troubleshooting Question

Access VBA Automatically link to back-end issue

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVBA
7 Comments1 Solution26 ViewsLast Modified:
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"
                      Else
                          .Connect = ";DATABASE=" & CurrentProject.Path & "/ArmsTracker Data.accdb"
                      End If
                 
                    .RefreshLink
                     
                     Debug.Print "New: " & .Connect         'Show me new
                     
                End If
            End With
        Next
       
        If InStr(N, "accde") > 0 Then
            MsgBox "Successful connection to " & CurrentProject.Path & "/ArmsTracker Data.accde"
        Else
            MsgBox "Successful connection to " & CurrentProject.Path & "/ArmsTracker Data.accdb"
        End If
       
        fRefreshLinks = True
    Exit Function
EH:
    MsgBox "DATA FILE RE-CONNECTION ERROR: " & Err.Description
    fRefreshLinks = False

End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros