ACCESS 删除/添加外链数据数

其中 数据表tblLinkedTables中,记录了所有需要外链的表名.

 核心代码如下:

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