Linked data sources

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top