Access Cookbook

Chapter 4.  Developing and Distributing Applications

This chapter is a compendium of tips and suggestions for making your application development go more smoothly and your applications look more professional. You’ll learn how to convert queries into embedded SQL strings providing data for forms or reports. You’ll learn how to build an object inventory so you can document your applications better, how to ensure that properties for objects that should match up actually do, and how to disable screen output more effectively than the methods Access provides internally can. You’ll find tips on discerning the current language version of Access and modifying text in error messages and on forms and reports to accommodate the current language. You’ll see how to set and restore the Access caption and how to set startup options for your application. You’ll also see how to use the Windows File Open/Save dialogs and how to clear out test data before shipping your application. The final topic explains how to implement user-level Access security.

Convert Queries into Embedded SQL Statements

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?

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.

1. 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

2. Click on the Build button (...) next to the RecordSource property to open the Query Builder for the record source query.

3. With the Query Builder open, click on the View SQL button on the toolbar or select View → SQL.

4. 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

5. Highlight the entire SQL statement and press Ctrl-C or select Edit → Copy to copy it to the clipboard.

6. Close the SQL window.

7. 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

8. Delete the original RecordSource query from the database container.

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.