'This function loop through all linked tables in MS Access database
'and re-link them to new database path
Public Function UpdateLinkTables()
    '
    Dim strPath As String
    Dim objDatabase As Object
    Dim tblDef As TableDef
    '
    strPath = "E:\Work\ExcelSirJi\Database.accdb"
    '
    'Set the object to current database
    Set objDatabase = CurrentDb
    '
    'Go through each table in the database and check if it is linked table
    'if yes, then update new database path
    For Each tblDef In objDatabase.TableDefs
        If tblDef.SourceTableName <> "" Then
            'If it is hidden table then make it a visible table
            tblDef.Properties("Attributes").Value = 0
            'Change the database path to new path
            'if database requires password then you can un-comment password section in below code
            tblDef.Connect = ";DATABASE=" & strPath '& "; PWD=1234"
            'Refresh the table
            tblDef.RefreshLink
        End If
    Next
    '
    'Close the objects
    Set tblDef = Nothing
    Set objDatabase = Nothing
    '
End Function