(un)linking tables in Access 2002

  • Thread starter Thread starter pvp
  • Start date Start date
P

pvp

Guys,

Is there a straightforward way to link and unlink to
tables from one Access databases to another in VBA? Is
there any funnies that I should look out for and correct
for?

Many thanks,

pvp
 
This code will do a basic table link:
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.CreateTableDef("LocalTableName")
tbl.SourceTableName = "SourceTableName"
tbl.Connect = ";DATABASE=" & "C:\Data\SourceDB.mdb"
db.TableDefs.Append tbl

To unlink, simply delete the local table:
CurrentDb.TableDefs.Delete "LocalTableName"
 
You make it sound really simple, oh Antipodean Advisor!
Any hidden horrors or gotchas I should look out for in
doing this?
Thanks again,

pvp
-----Original Message-----
This code will do a basic table link:
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.CreateTableDef("LocalTableName")
tbl.SourceTableName = "SourceTableName"
tbl.Connect = ";DATABASE=" & "C:\Data\SourceDB.mdb"
db.TableDefs.Append tbl

To unlink, simply delete the local table:
CurrentDb.TableDefs.Delete "LocalTableName"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Guys,

Is there a straightforward way to link and unlink to
tables from one Access databases to another in VBA? Is
there any funnies that I should look out for and correct
for?

Many thanks,

pvp
Click to expand...


.
Click to expand...
 
I've been called lots of names, but never one of them before :-)

It gets a little more tricky if the back-end database has a password on it,
or is secured with user-level security, but this just means you need to add
more options to the Connect string. Actually, with user-level security it
is very rare for the back-end database to be secured with a different MDW
from the front-end, so it's not a problem and nothing extra is required.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

pvp said:
You make it sound really simple, oh Antipodean Advisor!
Any hidden horrors or gotchas I should look out for in
doing this?
Thanks again,

pvp
-----Original Message-----
This code will do a basic table link:
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.CreateTableDef("LocalTableName")
tbl.SourceTableName = "SourceTableName"
tbl.Connect = ";DATABASE=" & "C:\Data\SourceDB.mdb"
db.TableDefs.Append tbl

To unlink, simply delete the local table:
CurrentDb.TableDefs.Delete "LocalTableName"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Guys,

Is there a straightforward way to link and unlink to
tables from one Access databases to another in VBA? Is
there any funnies that I should look out for and correct
for?

Many thanks,

pvp
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top