لطفا منتظر باشید ...
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.
- Open the form whose record source you want to convert to a single SQL
statement in design view, and make sure that the properties sheet is
open (Figure 4-1).
Figure 4-1. A form's properties sheet, with a query as its RecordSource property
- Click on the Build button (...) next to
the RecordSource property to open the Query Builder for the record
source query. - With the Query Builder open, click on the
View SQL button on the toolbar or select View
SQL. - The SQL window opens, displaying the query as a SQL statement, as
shown in Figure 4-2.
Figure 4-2. The SQL window for a simple query
- Highlight the entire SQL statement and press Ctrl-C or select Edit Copy to copy it to the clipboard.
- Close the SQL window.
- Highlight the query name in the
RecordSource properties sheet and press Ctrl-V or select Edit Paste to replace the query name with the SQL statement.
Figure 4-3 shows the form's
RecordSource property with the SQL statement in place.
Figure 4-3. A form's properties sheet with a SQL statement as its RecordSource property
- Delete the original RecordSource query from the database container.
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
choiceyou 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.
