Selecting All Tables 1

[ching0418]

hello all,

how can i display all the tables of a database?

thanks!

ching

 

[thegameoflife]

On the Tools menu, click Options.


Click the Tables/Queries tab.


Select or clear the Show Table Names check box.


---
from help in access

 

[ching0418]

what i mean is through SQL statement, how can i select all the tables exiting in my database?

 

[JonFer]

This lists the tables actually in your database (not linked).

SELECT Name
FROM MSysObjects
WHERE Flags=0 AND Type=1;

You can set the view options to allow you to see the system tables. MSysObjects has all of the objects in your database.

 

[ching0418]

thanks for that JonFer

how about counting the number of fields and selecting all the field names of a table?

 

[JonFer]

Use VB to do something like this looking through each table in the TableDefs collection:

For Each myTable in CurrentDb.TableDefs
For Each Field1 In myTable.Fields
Debug.Print myTable.Name & " " & Field1.Name & " " & Field1.Properties("Description&quot
Next
Next

You'll need error checking if some fields don't have a description. I used something like this to write the fields for all of the tables in a database to a table.