Using SQL Statements with Forms, Reports, and Controls

If you create many forms and reports based on queries, views, or stored procedures, the query list in your Database window can become cluttered. The clutter becomes worse as you add queries, views, or functions to populate list and combo boxes. You can use SQL queries you write or copy from the SQL dialog in place of the names of query or view objects as the data source for forms, reports, and lists. After you verify proper operation of the object whose data source you changed, delete the corresponding object from your database. You can use Jet SQL or T-SQL statements for the following purposes:

  • Record Source property of forms and reports. Substitute the SQL query text for the name of the query in the Record Source text box.

  • Row Source property in lists and drop-down combo lists on a form. Using an SQL statement rather than a query or view object can give you greater control over the sequence of the columns in your list.

  • Value of the SQL property of a QueryDef object or the strSource argument of the OpenRecordset method in VBA code for Jet databases. You use SQL statements extensively as property and argument values when programming applications with VBA, especially for SQL pass-through queries.

  • graphics/2002_icon.gif Source property of a DAO.Recordset object specified as the Recordset property of a Access form, report, or control. The capability to bind Access objects to DAO.Recordset objects was new in Access 2002.

  • Source property of an ADODB.Recordset object specified as the Recordset property of a form, report, or control. Access 2000 introduced the capability to bind Access form and report objects to ADO Recordsets. Access 2003 ADP can use disconnected Recordsets to minimize active connections to the database.

You can create and test your Jet SQL statement in Query Design view or the project designer's SQL pane for views. You can copy unformatted Jet SQL statements directly to the Clipboard. Paste text of T-SQL statements formatted by the project designer into Notepad, remove the formatting (with WordWrap on), and copy the unformatted text to the Clipboard. Paste the text into the text box for the property or into your VBA module. Then close the test query or view design without saving it.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net