Recipe 4.1 Convert Queries into Embedded SQL Statements

4.1.1 Problem

Access's Query Builder makes it easy to create SQL statements as row sources for combo boxes or as record sources for forms and reports. You'd prefer to use SQL statements for row and record sources because they reduce the number of unnecessary objects in your databases. Is there an easy way to make these conversions? What's the trade-off of using embedded SQL statements instead of query objects to provide your data?

4.1.2 Solution

There is no automatic conversion utility to transform queries into SQL statements, but you can use the View SQL button on the Query Design toolbar to display a query's SQL statement, copy it to the Windows clipboard, and then paste it into the RecordSource or RowSource property of a form or combo box.

Open 04-01.MDB and look at the form frmCompanyInfoQuery. This form has a simple query as its record source; the combo box in its header also has a query as its row source. Neither of these queries is needed elsewhere, so they are prime candidates for conversion into SQL statements.

Take the following steps to convert a query, using the form's record source query as an example. These steps have already been taken for the form frmCompanyInfoSQL, both for the form's RecordSource property and for the combo box's RowSource property.

Figure 4-1. A form's properties sheet, with a query as its RecordSource property

Figure 4-2. The SQL window for a simple query

Figure 4-3. A form's properties sheet with a SQL statement as its RecordSource property

4.1.3 Discussion

Most Access queries can be converted back and forth between the graphical representation shown in the Query Builder window and the SQL representation of the query. The SQL window makes it easy to extract a query's SQL statement and use it directly as a record source or row source or in VBA code. Because all queries in Access can be represented as SQL statements, you have a choice—you can base a form or report on a query, or you can supply the SQL string directly in the properties sheet.

Converting row source queries into SQL statements lets you eliminate many trivial queries that have no purpose other than filling forms or combo boxes. If you have a SQL statement as a record or row source, you can open the Query Builder window to view or modify it, which makes it easy to use SQL statements in place of queries. Access always saves your SQL statements as hidden queries in the background, anyway, so you still get the slight performance benefit of having the execution plan for the query saved rather than recalculated each time the query runs.

We should mention a few caveats. First, if you use the same complex query as a row source for several different database objects, especially if you anticipate changing the query, it may be best to leave the query as a query object rather than converting it into a SQL statement. If you use one query as a record source for several forms or reports, when you change the query all the forms or reports that use it will pick up the changes. Also, there are some query properties that apply only to saved queries, such as the RunPermissions property. If you need to use these properties in a secured database, you must leave the queries as query objects.

In some cases, you may need to convert a SQL statement into a query (for example, if you need to use it as a record source for several forms or reports). In that case, simply reverse the steps given earlier: open the SQL statement in the Query Builder window and then save it as a named query, which you can use as a record source for other database objects.

In addition, you can use the Query Builder to help create a row source or control source from scratch. Simply click on the Build button and build a SQL statement as though you were building a query. Rather than saving a query object in the database container, Access will save the SQL string you've created into the appropriate property.

4.1.4 See Also

For more information on working with queries, see Chapter 1.