Link table

[QUINABA]

Good Friday to all!

Is it possible to link a table from database "A" to "B" while being in database "C" utilizing a macro? I usually run a bunch of adhoc reports using the same tables and normally would have to create duplicate mdb's to be able to multi-task.

Thanks.

 

[QUINABA]

Thanks Peter, you are awesome! It works great!

Hate to be a pest, what's the best way to modify when linking multiple tables from the same mdb or perhaps linking multiple tables from multiple mdb's.

 

[bat17]

call it from another sub and pass the values for table names that way.

Sub MakeLinks()
Dim strSourceTable As String
Dim strSourceDB As String
Dim strTargetDB As String
Dim strTargetTable As String

strSourceDB = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" 'path to source DB
strTargetDB = "C:\DB1.mdb" ' Database to put link in

strSourceTable = "Employees" ' table to link from
strTargetTable = "Employees" ' name for linked table
Call linkTable(strSourceDB, strSourceTable, strTargetDB, strTargetTable)

strSourceTable = "Customers" ' table to link from
strTargetTable = "Customers" ' name for linked table
Call linkTable(strSourceDB, strSourceTable, strTargetDB, strTargetTable)

strSourceTable = "orders" ' table to link from
strTargetTable = "orders" ' name for linked table
Call linkTable(strSourceDB, strSourceTable, strTargetDB, strTargetTable)

End Sub

Sub linkTable(strSourceDB, strSourceTable, strTargetDB, strTargetTable As String)
Dim strConnect As String
Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = OpenDatabase(strTargetDB)
strConnect = ";DATABASE=" & strSourceDB
Set tdfLinked = dbsTemp.CreateTableDef(strTargetTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked

Set tdfLinked = Nothing
dbsTemp.Close
Set dbsTemp = Nothing
End Sub

Peter

 

[QUINABA]

Again, AWESOME!!! VERY INGENIOUS... ray: ray: ray: ray: ray: