Text box listing back end data location

[matt beamish]

Id like to include a text box on a form showing the location of back end data.
Ive found the way to list the current database location with data source as

=[CurrentProject].[FullName]

Can anyone advise how I could use something similar for a linked table?
Thanks
Matt

 

[HiTechCoach]

Directly from Access Help file on the topic,

Connect Property: Sets or returns a value that provides information about the source of an open connection, an open database, a database used in a pass-through query, or a linked table. For Database objects, new Connection objects, linked tables, and TableDef objects not yet appended to a collection, this property setting is read/write. For QueryDef objects and base tables, this property is read-only.

As a test, run the sListPath sub in your database. Note that for local tables, a blank line, and for linked tables, the path to their parent database will be printed out in the Debug window.

For general usage, pass individual table names to fGetLinkPath function.

'*************** Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
'
Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

    Set dbs = CurrentDb()
    On Error Resume Next
    stPath = dbs.TableDefs(strTable).Connect
    If stPath = "" Then
        fGetLinkPath = vbNullString
        'can change this to currentdb.name
    Else
        fGetLinkPath = right(stPath, Len(stPath) _
                        - (InStr(1, stPath, "DATABASE=") + 8))
    End If
    Set dbs = Nothing
End Function

Sub sListPath()
    Dim loTd As TableDef
    CurrentDb.TableDefs.Refresh
    For Each loTd In CurrentDb.TableDefs
        Debug.Print fGetLinkPath(loTd.Name)
    Next loTd
    Set loTd = Nothing
End Sub

'*************** Code End **************