Selecting All Tables Within a Database

[BigxRed]

I am trying to select all of the tables within an Access database. Is it possible to select all of the tables within a database, even though all of the tables have different field names and different number of fields? I have surfed the web to find some instances of the usage of something called information_schema.some_parameter. What exactly is information_schema? I do not understand how information_schema can be used in order to achieve this.

I am currently in the process of creating a multiple query project. However, the first step involves the user selecting which table they had used within the db. Any help would be greatly appreciated.

 

[BigxRed]

I should also note that I have attempted to use the information_schema in the following way:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

I am not sure if this is even correct. However, even when I try to run it within MS Access, I am given an error message stating that information_schema is not within the correct file directory.

 

[dhookom]

You might want to use a query with sql like:

SELECT msysobjects.NAME
FROM msysobjects
WHERE (((msysobjects.NAME) Not Like "msys*") AND ((msysobjects.TYPE) In (1,4,6)));

I would typically not display my table names since they are for human consumption. I would recommend creating queries and providing a list of query names that you maintain in a table of query names.

Duane
Hook'D on Access
MS Access MVP

 

[sxschech]

Are you saying you want a list of table names (not the fields that make up the tables) to show in a drop down box on a form? If so:

In the row source of your combobox:

SELECT NAME FROM msysobjects WHERE TYPE=1;


If you also want to show linked or other items such as queries and reports, then create a query in SQL view:

SELECT * FROM msysobjects;

And you can then determine the additional TYPE(s) you need to add to the row source query in order to display them.