Private Sub cmdBrowse_Click()
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = CurrentProject.path & "\"
        .Title = "選擇后端數據庫"
        .Filters.Add "Access 數據庫", "*.accdb"
        If .Show Then Me.txtDBPath = .SelectedItems(1)
    End With
    '刪除所有鏈接表
    DelLink
    '重新添加鏈接表
    ReSetLink Me.txtDBPath
End Sub

Public Sub DelLink()
     ' 刪除所有鏈接表
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Len(tdf.Connect) > 0 Then CurrentDb.TableDefs.Delete tdf.Name
    Next
    CurrentDb.TableDefs.Refresh
End Sub

'鏈接無密碼的數據表
'Public Sub ReSetLink(ByVal DBPath As String)
'    On Error Resume Next    '避免無效的記錄
'        ' 重新鏈接表
'    Dim beDB As DAO.Database: Set beDB = OpenDatabase(DBPath)
'    With beDB.OpenRecordset("SELECT TableName FROM tblLinkedTables")
'        Do While Not .EOF
'            DoCmd.TransferDatabase acLink, "Microsoft Access", DBPath, acTable, !TableName, !TableName
'            .MoveNext
'        Loop
'        .Close
'    End With
'    beDB.Close
'End Sub

'重新鏈接所有數據表
Public sub ReSetLink(ByVal DBPath As String) 
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim tblName As String
    Dim conTXT As String
    conTXT = ";database=" & DBPath & ";pwd=123456789"
    Set rs = CurrentDb.OpenRecordset("select TableName From [" & conTXT & "].tblLinkedTables ")
    While Not rs.EOF
        tblName = rs.Fields("tablename").Value
        ' 創建鏈接表定義
        Set tdf = CurrentDb.CreateTableDef(tblName)
        ' 設置鏈接表的連接信息
        tdf.Connect = conTXT
        tdf.SourceTableName = tblName
        ' 添加新的鏈接表到數據庫
        CurrentDb.TableDefs.Append tdf
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    CurrentDb.TableDefs.Refresh     '刷新數據表列表
End sub