In addition’ to tables, a database can contain query definitions. The queries that are commonly used on !l database can be stored in the database and called by name. All the queries stored in database can be accessed through the QueryDefs object, which is similar to the TableDef object. The QueryDefs object is a collection of QueryDef objects, one for each stored query. You can access the following properties of the QuetyDefs collection from within your code:
- Count returns the number of queries stored in a database.
- Name returns the name.of the query.
- SQL returns the SQL statement of the query.
The number of queries stored in a database is given by the following expression:
DB.QueryDefs.Count
To access all queries stored in a database, declare a QueryDef object variable as
follows: .
Dim qry As QueryDef
and then scan the elements of the QueryDefs collection with a For Each … Next loop:
For Each qry In DB.QueryDefs
Debug,Print qry.Name
Next
VB6 at Work: The DBStrudure Projected
The DB Struct application, shown in Figure 17.24 earlier in this chapter, lets you open any database and view the names of its tables, their structure, the queries stored in the database, and their definitions, The tables .and queries of the database are displayed in the two lists on the left. Under each. table’s name you see the names of the indices for the table. Click the name of a table to display its fields, and click the name of a query to display its definition.
Along with the filenames, the program displays the names of the indices for each table below the name of the corresponding table and indents them two spaces.
Let’s look at the basic parts of.#Us subroutine. The user-specified database is opened and assigned to the DB object variable. It then scans the members of the TableDefs object and appends the name of each table to the Tables list. Notice the , following If structure:
If Left(tbl.Name, 4) <> ‘MSysi And Left(tbl.Name, 4) <>..’USys’
Along with the tables you specify when You design a database, Microsoft Access databases contain a few system tables. The JET engine uses these, and you need’ not manipulate them from within your code, Actually, you shouldn’t. System table names begin with the prefixes MSys and USys. The program ignores these tables with the previous If statement.
After displaying the name of the current table, the program scans the indices of the table with the help of the current table’s Indexes object. After all table and index names are displayed, the program displays the names of all queries stored in the database.
When a table name is clicked in the.Tables list, its fields are display.ed in the Selected Table’s Fields list. Likewise, when a query name is clicked, its definition (a SQL statement) appears in the Selected Query Definition textbox.
If the item that is clicked begins with two spaces, it’s an index name, and the program doesn’t react. If the item does not begin With two spaces, the program clears the ListBox control where the field names will be displayed and starts filling the list with each field’s name. To access the names of the fields, the program scans the elements of the DB.Tab1eDefs(TblList.Text). Fie1ds collection. With each iteration, it appends the name of another field (fld.Name)to the list. You can also display the field’s type with the type property and the field’s size (in bytes) with the Size property.
When a query name is clicked, its definition appears in the multi line textbox.
, ,
Notice that the objects of the Table~ are accessed by the name of the table and QueryDefs are accessed by the name of the.stored query. The alternative is to access them with an index. since the system tables were omitted, the order of the tables’in the Tables list is not the same as the order of the tables in the database, therefore this method would not work for this application.
