如何用代码创建表的链接?

cjls2000

想实现 通过VBA代码 实现对表的 创建链接  和 断开链接

tmtony

给你一段参考的代码,或许能帮到您:

Function CreateLinkedExternalTable(strTargetDB As String, strProviderString As String, strSourceTbl As String, strLinkTblName As String) As String
   
      Dim catDB As ADOX.Catalog
    Dim tblLink As ADOX.Table
    Set catDB = New ADOX.Catalog

    catDB.ActiveConnection = "rovider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sSourceDBFile
    If catDB.ActiveConnection.State = adStateClosed Then GoTo ErrHandler
   
    Set tblLink = New ADOX.Table
    With tblLink
         .Name = strSourceTbl
        Set .ParentCatalog = catDB
        'Set the properties to create the link.
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDBink Provider String") = "rovider=ODBC;DSN=TestODBC;DATABASE=TestDB;UID=SAassword=xxxxx" 'strProviderString
        .Properties("Jet OLEDB:Remote Table Name") = "dbo." & strLinkTblName
    End With
     On Error Resume Next '如果表不存在则继续
    catDB.Tables.Delete strSourceTbl
   
    On Error GoTo ErrHandler
    catDB.Tables.Append tblLink
    Set catDB = Nothing
   
    Exit Function
ErrHandler:
    MsgBox Err.Number & "  " & Err.Description
    Set catDB = Nothing
End Function