Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Improving Performance and Reusability by Basing Reports on Stored Queries or Embedded SQL Statements

Basing your Access reports on stored queries offers you two major benefits:

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

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

With earlier versions of Access, reports based on stored queries opened 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 earlier versions of Access, if you ran a report based on an embedded SQL statement, the query was compiled, and the query plan was built at runtime, slowing the query's execution. With Access 2002 and Access 2003, query plans are built for embedded SQL statements when the 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? Often, you 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 modify it once 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 the embedded SQL statement. On the other hand, many complex expressions are used in multiple reports and forms. By building these expressions into queries on which the reports and forms are based, you have to create the expression only one time.

TIP

It's easy to save an embedded SQL statement as a query. This 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, bring up the Properties window. Click the Data tab; then click on the Record Source property and click the ellipsis. The embedded SQL statement appears as a query. Select File Save As, enter a name for the query, and click OK. Close the Query window, indicating that you want to update the Record Source property. Your query is now based on a stored query instead of an embedded SQL statement.

Although you can see that basing reports on stored queries offers several benefits, it has its downside as well. If your 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 for embedded SQL statements rather than stored queries.