创建外部链接表示例

CreateLinkedTable()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim aLocalLinkedTables() As String
    Dim i As Integer
    Dim cSourceDATA As String
    Dim cLinkedTableList AS String
    Set cat = New ADOX.Catalog
    ' THE TABLES YOU ARE GOING TO LINK
    cLinkedTableList = "TABLE1,TABLE2,TABLE3"
    ' Open the catalog FROM your MAIN_DB
    cat.ActiveConnection = YourConnection
    cSourceDATA = "c:/DB_WHERE_LINKS_SOURCE_ARE.MDB"
    aLocalLinkedTables = Split(cLinkedTableList, ",")
    On Error Goto ErrAdm:


    For i = 0 To UBound(aLocalLinkedTables)
        ' Create the new Table.
        Set tbl = New ADOX.Table
        tbl.Name = aLocalLinkedTables(i)
        Set tbl.ParentCatalog = cat
        ' Set the properties to create the link.
        '    
        
        tbl.Properties("Jet OLEDB:Link Datasource") = cSourceDATA
        'IF THE LINKED DB HAS PASSWORD USE THIS
        '     LINE, ELSE IGNORE
        tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=LINKED_DB_PWD"
        tbl.Properties("Jet OLEDB:Remote Table Name") = aLocalLinkedTables(i)
        tbl.Properties("Jet OLEDB:Create Link") = True
        
        ' Append the table to the Tables collect
        '     ion.
        cat.Tables.Append tbl
        Set tbl = Nothing
    Next
    Set cat = Nothing
    On Error Goto 0
    Exit Sub
    ErrAdm:


    If Err.Number = -2147217857 Then
        'LINK ALREADY EXIST, REFRESH THE DATA
        Debug.Print "LINK ALREADY EXIST, REFRESH THE DATA: " & aLocalLinkedTables(i)
        tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=LINKED_DB_PWD"
        tbl.Properties("Jet OLEDB:Link Datasource") = cSourceDATA
        Resume Next
    Else
        'SOMETHING ELSE HAPPENED
        DEBUG.PRINT Err.Number & " - " & Err.Description
    End If
End Sub