can we list all tables in msaccess database using sql?

Question

Can we find all tables in the msaccess using sql .

as we do in sqlserver

select * from sys.tables  

in sqlite

SELECT * FROM sqlite_master where type='table' 

Darsie

Answer 1

Use MSysObjects

SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

Pervert

That throws this error: OLE DB provider "MSDASQL" for linked server "TEST" returned message "[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'.". I mapped sa to Admin. What is required to get the results? – Amboceptor

Just found this: social.msdn.microsoft.com/Forums/sqlserver/en-US/… "Because MSysObjects is a system table in Access, the Admin user does not have permission to read data in it." – Amboceptor

Answer 2

Ms Access has several system tables that are, by default, hidden from tables list. You can show them.

In Ms Access 2007 do a right click on tables list and select Navigation Options. At the bottom of the form you will find Show System Objects check box. Check it and system tables will show up in tables list. They all start with MSys.
Alternatively, options form can be activated from application menu - click button Access options -> select Current Database and there is Navigation Options button.

Now you can examine structure and contents and generate queries of all system tables with MsAccess tools.

As Alex answered, table information is in MSysObjects

Gravois

Answer 3

The following query helped me scope a redesign/migration from MS Access to C# & SQL Server.

Note: Combines answers provided by both Alex K. and KTys.
Posted here with the belief that it will be useful to someone else (or myself if I have to do this again)

SELECT
  SWITCH (
    [type]=-32764,'Report' ,
    [type]  =  1, 'Table, local' ,
    [type]  =  3, 'obj Containers' ,
    [type]  =  4, 'Table, link odbc' ,
    [type]  =  5, 'Query' ,
    [type]  =  6, 'Table, link access' ,
    [type]  =  8, 'SubDataSheets' ,
    TRUE, [type]
  ) AS [type name (or #)]
  , name AS [Table Name]
FROM
  MSysObjects 
ORDER BY 
  2, 3

Note warning from KTys (type numbers are subject to change)
Add , * to the select clause to see the other fields (such as connect); they weren't helpful to me.

Created/tested with MS Access 2013

Resale

Answer 4

For Access 2013, I've used

SELECT name FROM MSysObjects WHERE type = 4

Martijn

Answer 5

This discussion gives a list of Type values. Be aware that MS does not guarantee same values from version to version.

Type    TypeDesc
-32768  Form
-32766  Macro
-32764  Reports
-32761  Module
-32758  Users
-32757  Database Document
-32756  Data Access Pages
1   Table - Local Access Tables
2   Access Object - Database
3   Access Object - Containers
4   Table - Linked ODBC Tables
5   Queries
6   Table - Linked Access Tables
8   SubDataSheets

Automata

Answer 6

SELECT name FROM MSysObjects where database <> ''

use this query to get the names of all the linked tables

Selfassurance