sql - Access get all tables

Question

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

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.