Path to linked table

[Guest]

Can someone tell me how to return or set the path to linked tables using VBA
code? I'd like to be able to tell whether the current database is linked to
production data or to test data. I'd also like to be able set the path to
all linked tables to production or test data at startup depending on user
options.

Thanks in advance,

 

[Douglas J Steele]

The current link can be read from the table's Connect property.

See http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web" for
one way to relink.

 

[Marshall Barton]

Can someone tell me how to return or set the path to linked tables using VBA
code? I'd like to be able to tell whether the current database is linked to
production data or to test data. I'd also like to be able set the path to
all linked tables to production or test data at startup depending on user
options.

That info is contained in the TableDef's Connect property.
For a Jet databse (Access), you can retrieve it using this
simple statement:

path = Mid(CurrentDb.TableDefs!tablename.Connect, 11)

For other database engines, you will have to locate it by
finding the ";DATABASE=" keyword:

Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb()
Set tdf = db.TableDefs!tablename
path = Mid(tdf.Connect,InStr(tdf.Connect,";DATABASE=")+10)