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

Creating a Link to an Access Table Stored in Another Database

   <source lang="vb">

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

</source>
   
  

Establishing a Link to an External Table

   <source lang="vb">

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

</source>
   
  

Linking to an External Table

   <source lang="vb">

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

</source>