Access get all tables

Question

Dear All,

Is there a way in by sql statement or vba code to return all tables from access file? "I don't know the name of the tables"

like when you want to have all fields in a table you use '*' regardless the names of the fields.

but how to get all tables?!!!!

Answer 1

Depends what kind of database you are running. Many of them support the SHOW TABLES command.

Answer 2

This will bring back all tables in the MS Access database (including linked tables)

SELECT MSysObjects.*, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1)) OR (((MSysObjects.Type)=6));

It also inclued Sys tables, so you might want to exclude tables starting with MSys

Have a look at

• Using MSysObjects
• SELECT "Table" AS [Table], MSysObjects.Name, MSysObjects.