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

Chapter 18. A Strategy to Developing Acc... > Basing Forms and Reports on Queries ...

Basing Forms and Reports on Queries or Embedded SQL Statements

You can base the record source for a form or report on a table object, a query object, or a SQL statement. By basing forms and reports on stored queries or embedded SQL statements, 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 or embedded SQL statement, 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 a 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 or embedded SQL statement, the server returns to the workstation just the fields and records specified within the query.

In Access 2.0, a form or report based on a stored query was more efficient than a form or report based on a SQL statement. This was the case because when you save a query, the Access database Jet Engine creates a Query Plan. This plan contains information on the most efficient method of executing the query. When you save a query, the Jet Engine looks at the volume of data and the available indexes, determines the optimal method of executing the query, and stores the method as the Query Plan. Jet uses this plan whenever it executes a query underlying a form or report. With Access 2.0, when you based a form or report on a SQL statement, the optimization process happened when the user opened the form or report, and Access executed the Query Plan on the fly. Subsequent versions of Access optimize an embedded SQL statement just like a stored query. It is therefore up to you whether you use a stored query or an embedded SQL statement as the foundation for your forms and reports.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


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