UNDERSTANDING WHERE QUERIES ARE USED IN ACCESS
Access queries come in three basic groups:
Select queries, which retrieve information from one or more tables or from other Select queries, including totals and crosstab queries
Action queries, which affect data in tables
Pass-through queries, which communicate with a SQL back-end database
This chapter covers Select and Action queries. Table 8.1 defines where you can use these queries in Access.
| Select Queries | Action Queries |
|---|---|
| Form record source | Macro OpenQuery action |
| Form filters | DoCmd OpenQuery in VBA |
| Report record source | Directly from the Database container |
| Source for queries | |
| Data Access Pages | |
| Subform source objects | |
| Input to other queries |
You can use Select queries in most of the same places that you can use a base table. A Select query provides a view of the data from one or more tables that's different from the way the data actually appears in the table. The data might be shown alphabetically in the query, even though the underlying table stores the data in the order it was entered into the table.
Using Queries with Form and Report Record Source Properties
A form or report's Record Source property can specify a query as a SQL statement or as a QueryDef. Using each option has advantages and disadvantages. Some developers prefer to use SQL statements rather than QueryDefs, so you can treat the form or report as a container and copy around the record source without worrying about losing the associated QueryDef. Figure 8.1 shows the form property sheet with a SQL statement as the source. By using this approach, you can edit the record source without accidentally affecting the source of another form or report.
Figure 8.1. Using a SQL statement for a record source saves having to keep track of another object (the query).
Tip
If the speed of the form load is critical, store the query as a QueryDef and be careful to follow a naming convention so that the query can easily be associated with the form or report it applies to.
Figure 8.2 shows the same record source stored as a QueryDef.
Figure 8.2. Query objects are good to use instead of SQL statements when the query is complicated.
Access compiles a SQL statement before storing it as a QueryDef, so part of the work required to solve the query is already done. This isn't true, however, of the SQL statements in a record source, which must be checked for syntax and then compiled before the query engine can begin to determine how to resolve the query. See the later section “Examining the Architecture of the Query Resolution Process” for more information on the query resolution process.
Giving Users Access to Queries
When developing end users, you might want to expose some of the queries directly to them. With Access security, you can allow users to run specific queries directly from the database container and restrict access to other queries that shouldn't be run. Although controlling which queries users have access to with security is quite effective, you might need to provide more information to users before they can run a query.
The query form example that follows, zsfrmQuery, explains how to expose Select and Action queries to users. The example allows new queries to be easily exposed to users as the system grows. Figure 8.3 shows the zstblQuery table, which is used with the zsfrmQuery form.
Figure 8.3. The zstblQuery table stores information on queries and allows users to run queries themselves.
In this example, the table tracks a short name that users can remember and a long description of what the query will do. The long description appears onscreen when the user clicks a query to select it (see Figure 8.4).
Figure 8.4. By providing users with an interface, you give them the power to safely run any of the queries they're allowed to run.
Users are presented with a form (zsfrmQuery) containing a list box that displays the queries from zstblQuery. The OnClick event for lstQuery shows the description of the query so that users can scroll down through each query. They can also read the information that describes the purpose of the query and when it should be run. When the table is in place, you can create as many queries as you like and expose them to users as needed without giving the users access to the database container.
Tip
You can add other information to this table to make the application more user-friendly. For example, you can add text that will appear in a confirmation prompt when the user clicks the Run button.
Using Naming Conventions and Query Documentation
One of the biggest messes a developer can create is in the query list in the database container. New business opportunities and concerns drive the need for new reporting and data analysis from the data that's collected in database tables. Developing a naming convention and documenting what queries are used for help keep the mess from getting out of control.
What developer hasn't received a call from a customer, “Hey, how many customers do we have in Washington now?” After a couple of clicks and dragging some fields, the answer is onscreen, and after some cleanup the query has the other five columns the customer needed. Tell them the answer and throw the query away, right? Not quite. You save the work in case the customer calls again tomorrow with the same question. Figure 8.5 shows the result of saving this query without using a specific convention for naming queries. Notice that a couple of queries are named simply Query1 and Query2. Not very descriptive, are they?
Figure 8.5. By not using naming conventions, you create a mess of meaningless names.
Two weeks later, you look at the query and try to remember what it was for. Was the query part of a form or report in the system, or just an ad hoc query run for the customer? The example just given points to the need for naming standards and query documentation.
Query definitions have a 255-character Description property that you can use to document a query's purpose. Combine this with a good naming convention such as LNC, and you have a good beginning (see Appendix D, “Leszynski Naming Conventions for Microsoft Access,” for LNC level 2 conventions). The naming conventions help convey the purpose of the query, especially when it's tied to a specific form or report. For example, qfrmAddr would be the query used as a record source for frmAddr.
Thanks to the number of queries in a database and the infrequent use of some queries, going into a more detailed description might be preferred. This is especially true when multiple summary queries are assembled into a final summary or union query that feeds a report. Figure 8.6 shows a modified version of zstblQuery, called zstblQuery_All, with a new date field added.
Figure 8.6. Adding the QueryTableDate field to the zstblQuery table allows you to keep track of when the query was added to the table.
Notice the addition of the query table date and the exposed flag. The table date is included to track the date the record was added to the table. Viewing the last date in the existing records provides a good date to reference when looking for changes in MSysObjects, a table that Access uses to keep track of information about objects such as queries and reports.
Note
The exposed flag is included so that all queries can be stored in one table and documented in one place. With the exposed flag, you can add query description information in the standard zstblQuery table and have the query form filter out the queries that shouldn't be exposed to users.
The query zsqupdQueryListNew updates this table with all new queries in the system. Run the query at regular intervals once or twice a day when developing a project or when the database is closed for the session. Documenting the queries and using standard naming conventions helps reduce the amount of time and energy spent combing through the query list in the database container.