Refresh link to HTML table?

[geoB]

In ACC2007:

I've built an application that, amongst other things, links to data that are imported as HTML tables into a back end database. The back end and original HTML documents are presumed to be present in the same directory as the front end database. A problem exists if the directory containing all of these changes.

I've tried several instances of code (discovered online, mostly here) to try to refresh the links of all the tables in the back end. The true Access tables can be readily refreshed. I just haven't stumbled on a method to refresh the links to the HTML documents. The problem, I'm reasonably certain, relates to the fact that the connect string for the HTML tables is different.

An example:
"HTML Import;DSN=ExamReport Link Specification;HDR=NO;IMEX=2;CharacterSet=65001;ACCDB=YES;DATABASE=C:...\ExamReport.html"

versus

";DATABASE=C:...\SN2BB_be.accdb"

Has anyone already solved this problem, or, if not, how do you suggest I go about solving it?

Thanks.

George

 

[geoB]

This works in the current situation. It's an adaptation of code I've used elsewhere.

Public Function CheckLinks() As Boolean

Dim dbLocal As Database
Dim tdf As TableDef
Dim strConnect As String
Dim fs, f
Dim localpath As String, localfile As String

    On Error GoTo ErrorCode                                             'trap errors including No Linked Table Found (see below)

    Set dbLocal = CurrentDb()
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    strConnect = Application.CurrentProject.path & "\SN2BB_be.accdb"
    Set f = fs.GetFile(strConnect)
    localpath = f.ParentFolder
    
    DoCmd.Hourglass True
    
    For Each tdf In dbLocal.TableDefs                               'loop through all (non-system) tables
        If Len(tdf.Connect) > 0 Then                                'skip if system table
            If InStr(tdf.Connect, "HTML") Then
                localfile = localpath & "\" & tdf.Name & ".html"
                tdf.Connect = Left(tdf.Connect, InStr(tdf.Connect, ";DATABASE=") + 9) & localfile
            Else
                tdf.Connect = ";DATABASE=" & strConnect           'set pathname + filename of back-end
            End If
            tdf.RefreshLink                                         'and make link to back end (if error go to ErrorCode)
        End If
    Next tdf                                                        'repeat till all tables linked
    DoCmd.Hourglass False
    
    CheckLinks = True                                                   'return with no error (CheckLinks = True)
    dbLocal.Close                                                       'close local database
    Set dbLocal = Nothing
    Exit Function                                                       'exit (CheckLinks = True)

ErrorCode:
    DoCmd.Hourglass False
    MsgBox Err & "  " & Err.Description                                 'show error and then
    CheckLinks = False                                                  'return False (and quit application) if serious error

End Function