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:

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:

sample results

0
There are no comments. Click here to write the first comment.