Query to obtain Schema and other info about the DB tables

[JohnS]

Is there a way to query Access (from within Access) to get a list of the
tables and queries in the DB?

I am trying to document a DB and would like to list out the table names and
get counts of tables without counting them by hand and retyping the names by
hand.

TIA

 

[Douglas J. Steele]

The following query will give you the tables:

SELECT [Name]
FROM MSysObjects
WHERE Name Not Like "MSys*"
AND [Type] In (1,4,6)
ORDER BY [Name]

(type 1 means tables in the same database, type 4 means tables linked using
ODBC, type 6 means other linked tables).

The following query will give you the queries:

SELECT [Name]
FROM MSysObjects
WHERE [Type] =5
ORDER BY [Name]

To avoid reinventing the wheel, you might check what Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/doctable.html

 

[JohnS]

Thanks.
Exactly what I was looking for.

The following query will give you the tables:

SELECT [Name]
FROM MSysObjects
WHERE Name Not Like "MSys*"
AND [Type] In (1,4,6)
ORDER BY [Name]

(type 1 means tables in the same database, type 4 means tables linked using
ODBC, type 6 means other linked tables).

The following query will give you the queries:

SELECT [Name]
FROM MSysObjects
WHERE [Type] =5
ORDER BY [Name]

To avoid reinventing the wheel, you might check what Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/doctable.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Is there a way to query Access (from within Access) to get a list of the
tables and queries in the DB?

I am trying to document a DB and would like to list out the table names
and
get counts of tables without counting them by hand and retyping the names
by
hand.

TIA