• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 12. Access Visual Basic Tools, T... > Creating a List of Objects

Creating a List of Objects

The Name and Type fields are accessed by the TableOfTables query. They are the keys to setting up a query accessing database objects. The type of object list you want is determined by the Type field and, of course, the object you want is determined by the Name field. The Name field is also where the system objects are excluded by using a wildcard in the criterion. Reverse the changes you just made in Tools, Options, View and take the following steps to examine and run the query:

Open the TableOfTables query in Design view.

Click the drop-down box of the View button and switch to Design view. Notice the Name column criterion excludes the system tables by using Not Like MS* for criterion. Notice also that the object type is “1” for table.

While in QBE view, click the Run button to retrieve the database tables as shown in Figure 12.6.

Figure 12.6. You can create a query that accesses the system objects, such as tables and queries, for enumeration.

Notice that all the tables are reflected dynamically, making the query continually current. This means that if you add a table and rerun the query, the new table will appear along with the others.

Go back to QBE and change the 1 in the Criterion row of the Type column to 5. Rerun the query and notice that all queries in the database are listed.

Perform step 4 using -32768 under Type for forms and -32764 for reports, and then change the Type back to 1.

Choose View, SQL to open SQL view. Select by highlighting (if not already highlighted) the entire SQL code and press Ctrl+C to copy the code.

Close the query without saving. You can now paste the copied SQL string into a form.

Under Forms, double-click the EnumerationForm to open it.

Click on the Enumerate Tables button, and cycle through the tables in the database. Click on the Enumerate Queries button as well. This is one way to enumerate objects. You will examine the code behind the buttons shortly.

Click the View button to enter Design view; then right-click the top list box and choose Properties.

Click on the Data tab, which contains the Row Source property.

Click on the Row Source property box and press Ctrl+V to paste the code copied in step 6 (see Figure 12.7).

Figure 12.7. Pasting the SQL string in the Row Source property box creates a dynamic reflection of objects in your database.

Don’t worry about the carriage returns in the SQL string. You see only one line of code at a time in the box, but you can simply click anywhere in the box and press the down arrow to see the rest of the string; or you right-click the box and choose zoom to see the entire string.

Click on the second list box labeled Dynamic List of Queries.

Paste the same data that was pasted in step 12 into the Row Source box, and then click on the Build button.

Change the 1 under Type to 5 in the QBE Design view to make the query retrieve query objects. Click Close and click Yes to save changes to the SQL statement. Close the Property sheet.

Click the View button to save and open the form.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint