Listing External Table Sources
Using the Parse() function to list linked tables by source. Use this when you want to refer to Linked Table Manager info while writing code.
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