Identify Path of Linked Tables

[rosieb]

I'd like to be able to display the full path of linked tables on a form, can anyone tell me where this information is buried? And how I could display it.

I know I can get it from the Linked Table Manager, but the box isn't sizable so I often can't see the full path.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[Ed2020]

You can retrieve it from the MSys tables.

Place this SQL statement behind a listbox on a form and it'll do all the work for you:

SELECT MSysObjects.Name, IIf([MSysObjects]![Database]<>"",[MSysObjects]![Database],CurrentDB.Name) AS Location
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*" And (MSysObjects.Name)<>"tblRestrictedText") AND ((MSysObjects.Type)=1)) OR (((MSysObjects.Name) Not Like "MSys*" And (MSysObjects.Name)<>"tblRestrictedText") AND ((MSysObjects.Type)=6))
ORDER BY MSysObjects.Name;


Please do not feed the trolls.....

 

[Molby]

Rosie,

If you go into the design view of the table and click on view/properties you should get the table properties, which includes in the description, the full path.

 

[KornGeek]

You can also get that information using code. An example of this (thrown together and completely untested, so use caution) would be something like:

Dim dbs as DAO.Database
Dim tdf as DAO.TableDef
Dim strPath as String

Set dbs = CurrentDb
For each tdf in dbs.TableDefs
  If (Len(tdf.Connect) > 0) then [green]'only linked tables have a connect string[/green]
    strPath = Right(tdf.Connect, len(tdf.Connect) - 10)
    [green]'Strip off leading ";DATABASE=" from connect string[/green]
    Debug.Print strPath
  End If
Next tdf

 

[rosieb]

Ed2020
That's perfect, just what I needed!

KornGeek
This doesn't seem quite so useful for what I want at this moment (OK, I find a query easier to manipulate) but I can see it coming in useful another time.

Molby
I'd tried "view/properties" but hadn't realised about opening the the table in design view.

Many thanks all.

I'm afraid that the star link isn't working, so it'll have to be virtual stars all round.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)