Microsoft Access supports tables of different types:

• Local - stored inside Access file
• Linked - ODBC - linked from external data source via ODBC
• Linked - stored inside external file (including Access, Excel, dBase)

You can query system table MSysObjects to get information about each user table in Access database:

Query

select 
   MSysObjects.name as table_name,
   switch (
      MSysObjects.type = 1, 'Local table',      
      MSysObjects.type = 4, 'Linked - ODBC',      
      MSysObjects.type = 6, 'Linked'
   ) as table_type
from MSysObjects
where
   MSysObjects.type In (1,4,6)
   and MSysObjects.name not like '~*'   
   and MSysObjects.name not like 'MSys*'
order by MSysObjects.name

Columns

• table_name - table name
• table_type - type of the table identified by the query:
  • Local,
  • Linked - ODBC,
  • Linked

Rows

• One row represents one table in a database
• Scope of rows: all tables in a database
• Ordered by table name

Sample results

You can see different table types in our test database: