VBA/Excel/Access/Word/Access/Database Link

Creating a Link to an Access Table Stored in Another Database

 
Sub LinkToAccess(strDBName As String, strTableName As String, _
   strAccessTable)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = New ADOX.Table
    tbl.Name = strAccessTable
    Set tbl.ParentCatalog = cat
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = strDBName
    tbl.Properties("Jet OLEDB:Link Provider String") = ";pwd=password"
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName
    cat.Tables.Append tbl
End Sub

Establishing a Link to an External Table

 
Sub LinkToDBase(strDirName As String, strTableName As String, _
   strAccessTable)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = New ADOX.Table
    tbl.Name = strAccessTable
    Set tbl.ParentCatalog = cat
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = strDirName
    tbl.Properties("Jet OLEDB:Link Provider String") = "dBASE III;HDR=NO;IMEX=2;"
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName
    cat.Tables.Append tbl
End Sub

Linking to an External Table

 
Sub LinkToAccessTableProps()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = New ADOX.Table
    tbl.Name = "tblLinkedTable"
    Set tbl.ParentCatalog = cat
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = _
        CurrentProject.Path & "\Data.accdb"
    tbl.Properties("Jet OLEDB:Link Provider String") = ";pwd=password"
    tbl.Properties("Jet OLEDB:Remote Table Name") = "tblClients"
    cat.Tables.Append tbl
End Sub