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>