Results 1 to 4 of 4
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Function to retrieve source of linked table

    I was dying to get the source database file name for a linked table into a diagnostic report (there were several possible sources, none with the same information) and after beating my head against the proverbial wall for a while, I figured I had to write my own function. You can paste this into any old Access 2000 module and call it in a query with the relevant table name as the parameter. If you haven't already done so, you will need to add a reference to Microsoft DAO 3.6 (Tools, References) or it won't compile.

    Function GetLinkName(strTable As String) As String
    'Jefferson 1/22/2001 - retrieve source table for linked table
    Dim oDB As Database, sLinkInfo As String, intPos As Integer
    Set oDB = CurrentDb
    sLinkInfo = oDB.TableDefs(strTable).Connect
    If Len(sLinkInfo) > 1 Then 'clean up the string and pass it back
    intPos = InStr(1, sLinkInfo, "DATABASE=") + 9
    GetLinkName = Mid(sLinkInfo, intPos, Len(sLinkInfo) - intPos + 1)
    Else 'it's not a linked table
    GetLinkName = "Not a linked table"
    End If
    End Function

    Numerous other table properties apparently can be accessed only through custom VBA functions. One of them is the actual name of the linked table itself (I only retrieved the file name). And now you have a template for that.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to retrieve source of linked table

    Open your database.
    Bring up the Options window.
    Check Hidden Objects and System Objects on the View tab.
    Look in the tables tab on the database window.
    Open table MSysObjects.
    Check out the Database field.

    Amazing what Access hides for you.....

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Function to retrieve source of linked table

    Cool. Some of that data is very ugly, but this does return the source database name, even after I re-hide the table:

    SELECT MSysObjects.Database
    FROM MSysObjects
    WHERE (((MSysObjects.Name)="MyTable"));

    'Should've posted here originally. No, I learned more this way. [img]/w3timages/icons/clever.gif[/img]

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to retrieve source of linked table

    Glad I could help.
    The forum is great way to learn a lot of the parts of access you are not using often.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •