Listing External Table Sources

Yesterday, I wrote about my Parse() function.  As a reminder, that function extracts values from a string formatted with key-value pairs.  Let's start putting that function to use now.

In my code library, I keep an entire standard module dedicated to routines that I use during development.  The module is called DesignProcedures and it includes things like my DocTests() function.

Today's entry is a procedure that lists all the linked tables in the database, their source, and a recap of how many tables are linked to that particular source.  This is the same information you get from the Linked Table Manager.  The problem with using the Linked Table Manager as a reference is that it is a modal dialog. That means you can't write code while its window is open. The ExternalDBSources function gives you access to that information in the VBIDE without having to revert to some awkward workaround like taking screenshots of the Linked Table Manager.

Usage - ExternalDBSources()

The below output is from the immediate window:

ExternalDBSources

--== Linked Table List ==--
Changes       Changes       ;DATABASE=C:\Users\Mike\Documents\TaxCollection\M202010.MDB
Ledger        Ledger        ;DATABASE=X:\M2020000.MDB
MBills        MBills        ;DATABASE=C:\Users\Mike\Documents\TaxCollection\M202010.MDB
MtgCoList     MtgCoList     ;DATABASE=C:\Users\Mike\Documents\TaxCollection\Settings10.mdb
MTownship     MTownship     ;DATABASE=C:\Users\Mike\Documents\TaxCollection\M202010.MDB
MTownshipPrev STownship     ;DATABASE=X:\S2019G.MDB
Parameters    Parameters    ;DATABASE=C:\Users\Mike\Documents\TaxCollection\M202010.MDB
PreviousBills MBills        ;DATABASE=X:\M202021.MDB
Receipts      Receipts      ;DATABASE=C:\Users\Mike\Documents\TaxCollection\M202010.MDB
ReceiptsPrev  Receipts      ;DATABASE=X:\M201921.MDB
SBills        SBills        ;DATABASE=C:\Users\Mike\Documents\TaxCollection\S202010.mdb
SBills1       SBills        ;DATABASE=X:\S202040.MDB
SBillsPrev    SBills        ;DATABASE=X:\S201921.mdb
School        School        ;DATABASE=C:\Users\Mike\Documents\TaxCollection\S202010.mdb
SchoolPrev    School        ;DATABASE=X:\S201921.mdb
Settings      Settings      ;DATABASE=C:\Users\Mike\Documents\TaxCollection\Settings10.mdb
STownship     STownship     ;DATABASE=C:\Users\Mike\Documents\TaxCollection\S202010.mdb
STownshipPrev STownship     ;DATABASE=X:\S201921.mdb
TCExpense     TCExpense     ;DATABASE=X:\M2020000.MDB
TLedger       TLedger       ;DATABASE=C:\Users\Mike\Documents\TaxCollection\M202010.MDB

--== External Sources Recap ==--
 6            C:\Users\Mike\Documents\TaxCollection\M202010.MDB
 2            X:\M2020000.MDB
 2            C:\Users\Mike\Documents\TaxCollection\Settings10.mdb
 1            X:\S2019G.MDB
 1            X:\M202021.MDB
 1            X:\M201921.MDB
 3            C:\Users\Mike\Documents\TaxCollection\S202010.mdb
 1            X:\S202040.MDB
 3            X:\S201921.mdb

The code: ExternalDbSources()

As noted above, this routine requires the Parse() function I wrote about yesterday.

'Print to the immediate window a detailed list of all linked tables by source
'   - include a recap showing how many tables are linked for each source
Sub ExternalDBSources()
    Dim Sources As New Collection
    Dim SrcCounts As New Collection
    
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Debug.Print vbNewLine; "--== Linked Table List ==--"
    Dim td As DAO.TableDef
    For Each td In db.TableDefs
        If Len(td.Connect) > 0 Then
            Dim DbName As String
            DbName = Parse(td.Connect, "DATABASE")
            If Left(td.Connect, 5) = "dBase" Then
                DbName = DbName & "\" & td.SourceTableName
            End If
            On Error Resume Next
            Dim Count As Integer
            Count = 0
            Count = SrcCounts.Item(DbName)
            SrcCounts.Remove DbName
            SrcCounts.Add Count + 1, DbName
            If Err.Number > 0 Then
                Err.Clear
                SrcCounts.Add 1, DbName
                Sources.Add DbName
            End If
            On Error GoTo 0
            Debug.Print td.Name, td.SourceTableName, td.Connect
        End If
    Next

    Debug.Print vbNewLine; "--== External Sources Recap ==--"
    Dim Src As Variant
    For Each Src In Sources
        Debug.Print SrcCounts(Src), Src
    Next Src

End Sub

Image by Rudy and Peter Skitterians from Pixabay