Results 1 to 5 of 5
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Get file name of linked table


    Given a table name, what's the method to obtain the file name to which the table is linked? I can loop through the tabledefs until I find the table and get its name at that point but how to get the file name with a single statement without a loop through tabledefs has managed to elude me.

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The function in Gina's link gives the correct path for a linked Access table but cuts off the first 5 letters (DRIVE) for a linked SQL table

    Alternatively you could use the MSysObjects table

    a) Linked Access
    Code:
    DLookup("Database","MSysObjects","Type=6 And Name='YourTableName'")
    a) Linked ODBC
    Code:
    DLookup("Connect","MSysObjects","Type=4 And Name='YourTableName'")
    If that looks useful I'll happily create a function for this approach
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Thanks Colin, PERFECT!

    Test code:
    Code:
     
    Option Compare Database
    Option Explicit
    Public Function WhereBE()
    Dim strStgPath As String
    Dim strCurBE As String
    
    strStgPath = Nz(DLookup("MyLinksPath", "tblSettings", "SettingsID = 1")) & "MyLinksData.mdb"
    strCurBE = Nz(DLookup("Database", "MSysObjects", "Type=6 And Name='tblDisplay'"))
    
    MsgBox strStgPath & "  =?  " & strCurBE
    End Function
    
    Results:
    Click image for larger version. 
    
    Name:	000.jpg 
    Views:	12 
    Size:	21.7 KB 
    ID:	35787

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-25-2016, 12:43 PM
  2. Replies: 4
    Last Post: 03-02-2015, 07:54 PM
  3. Replies: 5
    Last Post: 01-29-2013, 06:00 PM
  4. Replies: 2
    Last Post: 12-27-2012, 09:37 AM
  5. How to update table if linked ot text file
    By cory_jackson in forum Import/Export Data
    Replies: 7
    Last Post: 01-03-2012, 03:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums