| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The following sections describe tools you can use to build queries in Office 2000.
The Microsoft Access query wizards (Simple Query Wizard, Crosstab Query Wizard, Find Duplicates Query Wizard, and Find Unmatched Query Wizard) step you through the process of creating commonly used queries. After you've created a query by using one of the wizards, you can open it in Design view, and then view it in SQL view.
The Access query design grid provides a simple visual tool for building saved queries. You can create a query in the query design grid and display the result set in Datasheet view to test it. Access generates the corresponding SQL statement for the query, which you can copy from the query’s SQL view.
From Visual Basic for Applications (VBA) code, you can use the ActiveX Data Objects (ADO) data definition language (DDL) to create saved SELECT queries. You can also create temporary and saved queries in an Access database by using the Data Access Objects (DAO) QueryDef object. For more information about creating queries programmatically, see Chapter 14, "Working with the Data Access Components of an Office Solution."
Note You can also create saved SQL pass-through, union, and data definition queries in Access. These are SQL-specific queries, however, so you can't use the query design grid; you must type the SQL statement directly into SQL view.
For more information about using the Access query tools, search the Microsoft Access Help index for "queries, creating."
Microsoft Excel includes Microsoft Query, a tool similar to the Access query design grid, which you can use to create queries and return the result sets to an Excel worksheet. You can also save queries created in Microsoft Query as database queries (.dqy). Within the Microsoft Query grid, click SQL on the View menu to view or edit the SQL statement that's associated with the query shown in the grid.
Note The syntax for an SQL string shown in SQL view in the Access query design grid is not identical to that of the SQL string shown in SQL view in Microsoft Query for the same query. In other words, you can't copy an SQL string from Access into Microsoft Query, or vice versa, without making minor modifications. You need to change brackets([ ]) to accent grave characters (`), which are found on the same key as the tilde (~). Also, a query that you create in Microsoft Query may include the path to the database within the SQL statement, which isn't allowed in Access.
You can also create a saved Web query (.iqy) in Excel. A Web query extracts data from text or tables on a Web page and imports that data into Excel. A Web query does not have a corresponding SQL statement because it pulls data directly from a Web page without modification. However, Web queries are useful for displaying data from a Web page in Excel, especially if that data is updated frequently. For example, some of the sample Web queries included with Excel 2000 retrieve stock quotes from the Microsoft Investor Web site. To try one of the sample Web queries, click Get External Data on the Data menu in Excel, then click Run Saved Query and select a Web query from the Run Query dialog box. To create a new Web query, click Get External Data on the Data menu, and then select New Web Query. In the New Web Query dialog box, enter the address for the Web page that contains the data you want.
Note A Web query (.iqy) file is a text file that specifies the URL of the Web page containing the data, any data to be posted, and options indicating how the data is to be retrieved and formatted. You can edit a Web query file in a text editor such as Notepad.
You can create a third type of saved query in Excel 2000, an online analytical processing (OLAP) query (.oqy). An OLAP query returns data from any OLE DB for OLAP provider, such as Microsoft SQL Server OLAP Services, which provides rapid, sophisticated data analysis on large volumes of data. For information about OLAP queries, see the Universal Data Access Web site at http://www.microsoft.com/data/. For information about Microsoft SQL Server OLAP Services, see the Microsoft SQL Server OLAP Services Web site at http://www.microsoft.com/sql/olap/.