Linked data sources
Results 1 to 4 of 4

Thread: Linked data sources
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Linked data sources

    Howdy

    I have been provided with an Access database (mdb format) which links to - I believe - many underlying SQL Svr tables/views/queries in at least one (possibly more) SQL Svr tables.

    My problem is that I don't have MS Access installed in my environment. Whilst I can request it, I probably don't really need Access but do need to know what the underlying tables/views/queries are that this db links to.

    Is there a script I can run either from Excel on my machine, or from Access on somebody else's machine, that will output a list of the databases and targets that the Access db is sourcing its data from?

    Thanks in advance.

    I'm currently using Office 2010 in case this is important.
    Last edited by Richard Schollar; Nov 6th, 2017 at 04:02 AM.
    Richard Schollar

    Using xl2013

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,441
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Linked data sources

    Running from an Excel module, try the code below and see if it works.
    I am using Office 2010, so I required a reference to the Microsoft Office 14.0 Access Database Engine Object Library (by using the browse button I found it at C:\Program Files\Common Files\Microsoft Shared\Office14\acedao.dll)

    Not sure if this dll is present without Access but I assume that you do have the acedao.dll even without MSAccess installed.

    Code:
    Option Explicit
    
    Sub foo()
    
    Dim wks As DAO.Workspace
    Dim tdf As TableDef
    Dim qdf As QueryDef
    Dim db As DAO.Database
    
    
    Set wks = DAO.Workspaces(0)
    Set db = wks.OpenDatabase("C:\myTemp\db1.accdb")
    
    For Each tdf In db.TableDefs
        If Len(tdf.Connect) > 0 Then
            Debug.Print tdf.Connect
        End If
    Next
    
    For Each qdf In db.QueryDefs
        If Len(qdf.Connect) > 0 Then
            Debug.Print qdf.Connect
        End If
    Next
    
    End Sub
    I include queries here because queries, not only tables) can be linked to database table with ODBC (that is usually how I do it, rather than linked tables). If push comes to shove and you have to be 100% sure you can also remote query databases using IN path_to_DB as part of a FROM clause - probably little known and rarely used so that may be safe to ignore for now.

    a linked table property will looks like this (this is a remote access table):
    ;DATABASE=C:\myTemp\Database1.accdb
    a linked query property will look like this (this is a remote sql server table):
    ODBC;Description=SomeDescription;DRIVER=SQL Server Native Client 11.0;SERVER=SomeServer;Trusted_Connection=Yes;DATABASE=SomeDatabase;

    I don't have an example of a sql server linked table in my database.
    Last edited by xenou; Nov 6th, 2017 at 12:23 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Linked data sources

    I love you!

    Brilliant - did what I wanted (although ended up using the SourceTableName property rather than the connection string).

    It's been 10 years since I last used DAO. I feel old...

    This db is a monstrosity of an Access db. Definite end user creation by people who knew just enough to be very dangerous

    I am hopeful I will get Access installed and I can see what calculations it is performing - thankfully because of you I now know where the source data is coming from.

    Thanks again!
    Richard Schollar

    Using xl2013

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,675
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Linked data sources

    If you want to do more than just look at that data on your side, did you try the Get External Data > From Other Sources in the Excel ribbon?
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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