Linked data sources

[Richard Schollar]

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.

 

[xenou]

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.

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.

 

[Richard Schollar]

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!

 

[Micron]

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?