Access Cookbook - Ken Getz [84]
In some cases, you may need to convert a SQL statement into a query (for example, if you need to use it as a record source for several forms or reports). In that case, simply reverse the steps given earlier: open the SQL statement in the Query Builder window and then save it as a named query, which you can use as a record source for other database objects.
In addition, you can use the Query Builder to help create a row source or control source from scratch. Simply click on the Build button and build a SQL statement as though you were building a query. Rather than saving a query object in the database container, Access will save the SQL string you've created into the appropriate property.
See Also
For more information on working with queries, see Chapter 1.
4.2. Build an Object Inventory
Problem
To document your application, you'd like to be able to create a list of all the objects in your databases, including their owners, date of creation, and date of last update. You're sure you can do it manually, but is there a better way to create a table containing all this information?
Solution
Access's Data Access Objects (DAO) can give you the information you need. By programmatically working your way through each of Access's container collections, you can add a row to an inventory table for each object in your application, storing information about that object. You should be able to use the techniques for this operation to write your own code for enumerating other collections in Access. There are a few tricks along the way, which this solution discusses, but in general this is a straightforward project.
To create an object inventory for your applications, take only two steps:
Import the form zsfrmInventory from 04-02.MDB into your own application.
Load and run the form. As it opens, it builds the object inventory, saving the data in zstblInventory. If you want to rebuild the inventory once the form's up, click the Rebuild Object Inventory button. This recreates the inventory table and fills it with information about all the objects in your database. Figure 4-4 shows the form once it's been run on a sample database.
Figure 4-4. The inventory-creating form once it's done its work on a sample database
TIP
This example form includes the Access system tables, which you may never have encountered. These tables are part of every Access database and are not cause for alarm. You can view them in the Database Explorer by choosing the Tools → Options menu and turning on the Show System Objects option.
Discussion
How this solution works is a lot more interesting than the final product. The object inventory itself can be useful, but the steps involved in creating the inventory may be more useful to you in the long run. All the code examples used in this section come from the form module attached to zsfrmInventory (in 04-02.MDB).
When the form loads, or when you click the Rebuild Object Inventory button on zsfrmInventory, you execute the following code. (The "zs" prefix, by the way, reminds you that zsfrmInventory is a "system" form, used only by your application. The z forces this form to sort to the bottom of the database container so you won't get it confused with your "real" forms.)
Private Sub RebuildInventory( )
On Error GoTo HandleErr
DoCmd.Hourglass True
Me.lstInventory.RowSource = ""
Call CreateInventory
Me.lstInventory.RowSource = "SELECT ID, Container, Name, " & _
"Format([DateCreated],'mm/dd/yy (h:nn am/pm)') AS [Creation Date], " & _
"Format([lastUpdated],'mm/dd/yy (h:nn am/pm)') AS [Last Updated], " & _
"Owner FROM zstblInventory ORDER BY Container, Name;"
ExitHere:
DoCmd.Hourglass False
Exit Sub
HandleErr:
Resume ExitHere
End Sub
This code turns on the hourglass cursor and sets the main list box's RowSource property to Null. (It must do this because it's about to call the CreateInventory procedure, which attempts to delete the table holding the data. If the list box were still bound to that table, the code