Search code examples
sqlsql-serverms-accesstablename

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

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?

Solution

  • 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