Basing Forms on Queries: The Why and How

One strategy when building forms is to base them on queries. By doing this, you generally get optimal performance and flexibility. Rather than bring all fields and all records over the network, you bring only the fields and records you need. The benefits of doing this are especially pronounced in a client/server environment where the query is run on the server. Even in an environment where data is stored in the proprietary Access file format (.mdb) on a file server, a form based on a stored query can take better advantage of Access's indexing and paging features than a form based on a table. By basing a form on a query rather than a table, you also have more control over which records are included in the form and in what order they appear. Finally, you can base a form on a query that contains a one-to-many join and view parent and child information as if it were one record. Notice in Figure 16.20 that the customer and order information appear on one form as if they were one record.

Embedding SQL Statements Versus Stored Queries

In versions of Access prior to Access 2000, stored queries offer better performance than embedded SQL statements. This is because when you save a query, Access compiles the query and creates a query plan, which has information on the best way to execute the query based on available indexes and the volume of data. In versions of Access prior to Access 2000, if a form is based on an embedded SQL statement, the SQL statement is compiled and optimized each time the form is opened. With Access 2000 and above, embedded SQL statements are compiled the same way as stored queries. You might ask whether with Access 2003 it is better to base a query on a stored query or on a SQL statement. This is my personal preference: If I plan to use the same or a similar query with multiple forms and reports, I build a query and base multiple forms and reports on that query. This keeps me from having to duplicate my efforts in building the query. If I have a query that is unique to the form, I build it as an embedded SQL statement. This eliminates the extra clutter of the query in the database container.