لطفا منتظر باشید ...
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.On the other hand, when you base a form on table data, you can't control the order of the records in the form, nor can you base the form on more than one table. You can't limit the records the form displays until after the user opens the form. If you base a form on a query or an embedded SQL statement, you can control the criteria for the form as well as the default order in which the form displays the records.Everything just mentioned applies to reports as well, except the order of the records, which you determine by how the report itself is sorted and grouped.TIPMany other techniques are available to you when displaying a form based on a large recordset. My favorite involves basing the form on only a single record at a time and changing the form's RecordSource property each time the user wants to view a different record. Another technique that I use is to base the form's RecordSource property on the value the user selects in a combo box in the Header section of the form. I use the After_Update event of the combo box to requery the form. Because the form's RecordSource uses the combo box value for criteria, the form displays the desired record. I cover these techniques, and others, in detail in Alison Balter's Mastering Access 2002 Enterprise Development .
