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

Chapter 2. A Strategy for Developing Acc... > Basing Forms and Reports on Queries

Basing Forms and Reports on Queries

The record source for a form or report can be based on a table object, a query object, or an SQL statement. By basing forms and reports on stored queries, you can improve the performance and flexibility of your applications. In most cases, you don't need to display all fields and all records on a form or report. By basing a form or report on a query, you can better limit the data transferred over the network. These benefits are most pronounced in a client/server environment. When you base a form or report on a table object, Access sends an SQL statement that retrieves all fields and all records from the database server. On the other hand, if the record source for the form or report is a query, just the fields and records specified within the query are returned to the workstation.

Many developers don't realize that basing a form or report on a stored query is more efficient than basing it on an SQL statement. When you save a query, the Access database Jet Engine creates a Query Plan, which contains information on the most efficient method of executing the query. When the query is saved, the Jet Engine looks at the volume of data as well as available indexes, determines the optimal method of executing the query, and stores the method as the Query Plan. This plan is used whenever a form or report based on that query is executed. When a form or report is based on an SQL statement, the optimization process happens when the form or report is opened, and the Query Plan is executed on-the-fly.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


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