Link database tables from Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Andy said:
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.
Click to expand...


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
 
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.
 
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"
 
Back
Top