[ Team LiB ] Previous Section Next Section

Basing Reports on Stored Queries or Embedded SQL Statements

Basing Access reports on stored queries offers two major benefits:

  • The query underlying the report can be used by other forms and reports.

  • Sophisticated calculations need to be built only once—they don't need to be re-created for each report (or form).

With versions of Access prior to Access 2000, reports based on stored queries open faster than reports based on embedded SQL statements. This is because when you build and save a query, Access compiles and creates a query plan. This query plan is a plan of execution that's based on the amount of data in the query's tables as well as all the indexes available in each table. In versions of Access prior to Access 2000, if you run a report based on an embedded SQL statement, the query is compiled, and the query plan is built at runtime, slowing the query's execution. With Access 2000, query plans are built for embedded SQL statements when a form or report is saved. Query plans are stored with the associated form or report.

So what are the benefits of basing a report on a stored query instead of an embedded SQL statement? You may want to build several reports and forms, all based on the same information. An embedded SQL statement can't be shared by multiple database objects. At the very least, you must copy the embedded SQL statement for each form and report you build. Basing reports and forms on stored queries eliminates this problem. You build the query once and then modify it if changes need to be made to it. Many forms and reports can all use the same query (including its criteria, expressions, and so on).

Reports often contain complex expressions. If a particular expression is used in only one report, nothing is lost by building the expression into an embedded SQL statement. On the other hand, many complex expressions are used in multiple reports and forms. If you build these expressions into queries on which the reports and forms are based, you have to create each expression only one time.

graphics/bulb_icon.gif

It's easy to save an embedded SQL statement as a query, and doing so allows you to use the Report Wizard to build a report using several tables. You can then save the resulting SQL statement as a query. With the report open in Design view, you bring up the Properties window. After you select the Data tab, click in the Record Source property and click the ellipsis (…). The embedded SQL statement appears as a query. You need to select File | Save As, enter a name for the query, and click OK. Then you close the Query window, indicating that you want to update the Record Source property. The query is then based on a stored query instead of an embedded SQL statement.


Although basing reports on stored queries offers several benefits, it has downside as well. For example, if a database contains numerous reports, the database container becomes cluttered with a large number of queries that underlie those reports. Furthermore, queries and the expressions within them are often very specific to a particular report. If that is the case, you should opt to use embedded SQL statements rather than stored queries. As a general rule, if several reports are based on the same data and the same complex calculations, you should base them on a stored query. If a report is based on a unique query with a unique set of data and unique calculations, you should base it on an embedded SQL statement.

    [ Team LiB ] Previous Section Next Section