Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Tables: Retrieve linked database namepath

Author(s)
Dev Ashish

(Q)    I need to retrieve path and filename of the external databases from which I've linked various tables, sort of doing a  Currentdb.Name on all attached databases.  How can I do this?

(A)    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 **************

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer