How can I find out what SQL Server tables are linked to MS Access?

Question

I inherited a MS Access front-end that has linked tables to SQL Server. The linked table names in MS Access do not match the table names in SQL Server. How can I find out what SQL server tables are actually linked to MS Access? Also, if I didn't know what SQL Sever the linked tables were connected to, how could I find that out?

Janellajanelle

Answer 1

You can use the tabledefs collection to check the connect property and the source table name.

CurrentDB.TableDefs("dbo_table_name").SourceTableName
CurrentDB.TableDefs("dbo_table_name").Connect

Or

Dim tdf As TableDef
Dim db As Database

    Set db = CurrentDb

    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> vbNullString Then
           Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        End If
    Next

Creation

Thanks! This worked, but is there a way to also list the name of the MS Access link too? Now that I have the SQL Server table names, I'm not sure what it is actually linked to in MS Access, since their names do not match. – Janellajanelle