Linking tables through code

[Alastair MacFarlane]

Dear Group,

I am trying to create a linked table between two
databases. I have:

DatabaseA has TableA and DatabaseB has no tables.

I would like the sub to be run from DatabaseA that opens
DatabaseB and creates a link in DatabaseB to TableA in
DatabaseA.

The code works OK if I am in DatabaseB and I run the code
to create a link with TableA in DatabaseA, but I have
tried to turn it around:

Private Sub CreateLink()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = DBengine.OpenDatabase("DatabaseA")
Set tdf = dbs.CreateTableDef("TableA")
With tdf
.Connect = ";Database=C:\DatabaseA"
.SourceTableName = "TableA"
dbs.TableDefs.Append tdf
End With
Set dbs = Nothing
End Sub

Can anyone help with how I can do this? Thanks again…

Alastair MacFarlane

 

[Douglas J. Steele]

You're opening DatabaseA, and you're trying to create a link to DatabaseA.
One of those two need to be different!

And I don't know whether it's typos, but you're missing the .MDB extensions.


Private Sub CreateLink()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = DBengine.OpenDatabase("C:\DatabaseB.MDB")
Set tdf = dbs.CreateTableDef("TableA")
With tdf
.Connect = ";Database=C:\DatabaseA.MDB"
.SourceTableName = "TableA"
dbs.TableDefs.Append tdf
End With
Set dbs = Nothing
End Sub

 

[Alastair MacFarlane]

Doug,

As ever, thanks for your help.

From the sample with Access, if you provide the data
provider then I would assume that you don't need to
provide the extension. The default provider also seems to
be Jet?

Thanks...

Alastair

 

[Douglas J. Steele]

AFAIK, you need to supply the extension. Note that it's possible to rename
your backend to something else to make it less obvious what it is, so just
because you're linking to a Jet database doesn't guarantee its extension is
MDB.