8

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?

Tuan
  • 514
  • 3
  • 8

1 Answers1

7

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • 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. – Tuan May 17 '12 at 14:59