Tell If Local Table Is A Linked Table In A Separate Database

Question

I just took over as an IT guy at a company that has a myriad of Access databases all with local tables. A database may have 20 local tables, and 10 of the tables are linked into different databases as linked tables.

Without opening each and every Access database, is there a way to tell from the source table if it is linked into a different database and if yes, which one?

EDIT -->
This is probably not a feasible solution, so let me phrase the question this way. If I open each individual database, is there a script that I can execute that will provide me a list of all linked tables and the source DB that the table is linked back to?

Answer 1

Try this:

Public Sub LoopThroughTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        ' ignore system and temporary tables
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            Debug.Print tdf.Name, tdf.Connect
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing
End Sub

Source: How to loop through all Tables in an MS Access DB (added tdf.Connect)

The above code will list the name of each table along with that table's connect string. For local tables, the connect string will be blank.

Here's the sample output from an Access DB that has one local table named "LocalTable", and one linked table (linked to an Excel file) named "LinkedTable":

LinkedTable   Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Temp\temp.xlsx
LocalTable