Identify table types in Microsoft Access

Article for: Azure SQL Database MySQL MariaDB

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:


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


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


  • 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:

sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.