Link database tables from Code

[Guest]

If you know the location of 2 databases and you know the names of the tables
involved, is it possible to link the tables in the first database to the
second database remotely from code in a third database.

I did search and the closest I got was :-

DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\Destinationdb.mdb",
acTable, ST, DT (where ST and DT are variables with source and destination
tables names)

The trouble with this is that it has to be called from the Local database -
not remotely from both.

 

[Marshall Barton]

If you know the location of 2 databases and you know the names of the tables
involved, is it possible to link the tables in the first database to the
second database remotely from code in a third database.

I did search and the closest I got was :-

DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\Destinationdb.mdb",
acTable, ST, DT (where ST and DT are variables with source and destination
tables names)

The trouble with this is that it has to be called from the Local database -
not remotely from both.

Right, that won't do it. Use DAO instead.

This air code should get you going in the right direction:

Dim db1 As Database
Dim tdf As TableDef
Set db1 = OpenDatabase("path to first mdb")
Set tdf = db1.CreateTableDef("tablename", , _
"tablename", _
";Database=path to second mdb")
db1.TableDefs.Append tdf
db1.TableDefs.Refresh
Set tdf = Nothing
Set db1 = Nothing

 

[Guest]

Thanks Marsh - that worked fine except I had to change the code from

Set tdf = db1.CreateTableDef("tablename", , _
"tablename", _
";Database=path to second mdb")

to

Set tdf = db1.CreateTableDef("tablename")
tbf.Connect = ";Database=path to second mdb"
tbf.SourceTableName = "tablename"

Trouble is - if I run it twice and change the second mdb name, the append
statement fails because the linked table already exists in the first database.

How do I delete the existing linked table? or do I just refresh without the
append? I tried this but the refresh didn't work on its own. There seems to
be 2 actions within TableDef, Refesh and RefreshLink?

Thanks.

 

[Marshall Barton]

The TableDefs.Delete method can be used to get rid of an
existing TableDef. But, it is probably easier to just
change the values in the TableDef's Connect and SourceTable
properties.

I think I would tend towards this kind of logic:

Dim bolTblExists As Boolean
For Each tdf In db1 'does tdf exist?
If tdf.Name = "tablename" Then
bolTblExists = True
Exit For
End If
Next tdf

If Not bolTblExists Then 'if not, create it
Set tdf = db1.CreateTableDef("tablename")
db1.TableDefs.Append tdf
db1.TableDefs.Refresh
End If
'link the tdf to the new db and table
tbf.Connect = ";Database=path to second mdb"
tbf.SourceTableName = "tablename"