Results 1 to 5 of 5

Get file name of linked table

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

    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
    141
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  3. #3
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,389
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,115
    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:	9 
    Size:	21.7 KB 
    ID:	35787

  5. #5
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,389
    You're welcome.
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

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

Similar Threads

  1. Replies: 5
    Last Post: 05-25-2016, 11:43 AM
  2. Replies: 4
    Last Post: 03-02-2015, 06:54 PM
  3. Replies: 5
    Last Post: 01-29-2013, 05:00 PM
  4. Replies: 2
    Last Post: 12-27-2012, 08: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, 02: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
  •  
Tech Forums: Microsoft Office Forums