Creating Links to External Databases
For basic list-management tasks, such as sorting, searching, grouping, and summarizing, Excel is an appropriate, easy-to-use tool. For large and complex databases, however, you have better choices, including Microsoft Access. Choose Access over Excel if any of the following statements is true:
You need to combine data from multiple tables.
You want to create custom data-entry forms and highly formatted reports.
You want to create a secure application that multiple users can work with simultaneously.
Your list contains more than 65,536 records.
You want to store sounds, pictures, or other data besides text and numbers.
If your database needs have outgrown Excel's list-management capabilities, let a more powerful program (such as Access) manage the data; then use Excel to chart and analyze the subset of data that you selected.
Note
Live queries to external databases are especially useful with PivotTable reports and PivotCharts. For a full discussion of how to combine queries with these powerful analytical tools, see Chapter 27, "Using PivotTables and PivotCharts."
Use Microsoft Query to pull information from an external database—a group of tables in Access, for example, or a companywide database such as Oracle or SQL Server. Use the Query Wizard to create simple queries and place a reference to an external data range on your worksheet. Use MS Query directly to create more complex queries.
Note
The most attractive part of Microsoft Query is that it uses Open Database Connectivity (ODBC) drivers to directly access data files in other formats. You do not need to have a particular database program installed; all you need is a copy of the data file or access to the server that contains the data you want to use.
Using the Query Wizard
To launch the Query Wizard and create a new query, follow these steps:
Choose Data, Import External Data, New Database Query. If this is the first time you've used the Query Wizard, the Windows Installer will prompt you to complete the installation. The Choose Data Source dialog box appears, as shown in Figure 25.19.
Figure 25.19.
If the data that you want to connect with is in a relational database, such as dBASE, FoxPro, or Access, click the Databases tab and choose the correct entry from the list. If the data is in another data source, such as Paradox, SQL Server, or Oracle, choose <New data source>. Make sure Use the Query Wizard to Create/Edit Queries is selected, and click OK.
If you chose the <New data source> option, Excel displays the dialog box shown in Figure 25.20. Give the data source a descriptive name, choose the correct ODBC driver, and click the Connect button to enter server names and other login information. If you chose one of the database formats in Step 2, you'll bypass this step.
Figure 25.20.
When the Select Database dialog box appears, choose the database file that contains the table or tables that you want to use. Click OK.
After you've finished these preliminaries, you'll see the Query Wizard. Work through each step, choosing options appropriate to the database format that you selected. If you chose Microsoft Access format, for example, you'll see the Choose Columns dialog box shown in Figure 25.21. Pick the fields that you want to add to the query, and click Next. Succeeding steps enable you to filter your query and set sorting options.
Figure 25.21. Select the fields that you want to add to your query.
In the wizard's final step, click the Save Query button to save your query settings under a descriptive name. This entry appears on the Queries tab of the Choose Data Source dialog box. The next time you need to access this data, you can rerun this query directly rather than going through the Query Wizard again.
Select the Return Data to Microsoft Excel check box, and click OK. A dialog box (see Figure 25.22) lets you choose whether to add the data to a location on the existing worksheet, to a new worksheet, or to a PivotTable. Make your choice and click OK.
Figure 25.22.
For maximum flexibility, choose a PivotTable as the location for the data your query returns, and then manipulate it using the techniques described in Chapter 27.
Integrating External Data into a Worksheet
When you use the Query Wizard to add data from an external source to a worksheet, you can edit it just as though you had typed it in yourself. But the data that you see is not a simple static display; instead, Excel maintains a live connection between the source database and the worksheet data. Use the buttons on the External Data toolbar to update the data and edit the query, if necessary. The advantage of this connection is that you can refresh your data at any time. For example, if your company keeps its current product inventory and sales records on a SQL Server database, you can create a query that downloads sales results for the past six months and identifies products that are low on inventory. By using charts and PivotTables to analyze the sales for each product, you can decide whether, when, and how much of each product to reorder.
If you saved your query, you can reuse it in a new worksheet at any time. Choose File, Open, and select Queries from the Files of Type list (each saved query has the extension .dqy).
When you reopen a worksheet that contains a query, use the Refresh button on the External Data toolbar to make sure that your worksheet contains the most up-to-date data.
For maximum control over query options, click the Data Range Properties button. In the External Data Range Properties dialog box (see Figure 25.23), you can choose a variety of useful options. For example, if the data is constantly updated, check the Refresh Control options to tell Excel that you want to update it regularly and auto matically. If the quantity of data is unpredictable and you've added your own formulas at the end of each row, adjust the Data Formatting and Layout options. To completely replace the existing contents of the sheet and add your custom formulas to each new row, choose the Overwrite Existing Cells and Fill Down Formulas options.
Figure 25.23. Use this dialog box to set data refresh options.
Using Microsoft Query to Create Custom Queries
For some situations, a fixed query is perfectly acceptable. But in other instances, you want the query to be flexible. For example, you might want to be able to enter the name of a particular customer when you open the query so that you can see all the data associated with that customer. Or, maybe you want to view information about a salesperson or product category. One of the most powerful ways to customize a query is to add parameters—when you run or refresh the query, you see a prompt asking you to fill in the specific information that you want to look for. To create a parameter query, follow these steps:
Use the Query Wizard to define the database, tables, and fields for your query. Skip the step that enables you to define a filter for your query.
In the final step of the Query Wizard, choose the View Data or Edit Query in Microsoft Query option. Microsoft Query opens.
Drag the name of the field that you want to use for the parameter from the Table box above to the criteria list below. Click in the Value box just below the field name, and enter the prompt that you want to appear each time you run the query, enclosing the prompt text in brackets.
Choose File, Save, and give the query a descriptive name. Then choose File, Return Data to Microsoft Excel.
The next time you click the Refresh button, you'll see a dialog box in which you can enter the data that you want to use as the query parameter (for instance, the name of a company or product). Click OK to kick off the search.
When you click the Edit Query button, Microsoft Query opens. If you've used Microsoft Access, this application will look extremely familiar. The basic controls are the same as those in the Query By Example builder in Access. Use the same basic techniques to add and remove tables, edit query parameters, and adjust the layout of data returned to the worksheet.
→ For a detailed discussion of Access queries, see "Using Queries for Extract Data from a Database".
Note
Microsoft Query is a surprisingly complex application that includes dozens of sophisticated options, and there isn't room to cover all of them here. For much more detail about connecting an Excel workbook to external databases, see Special Edition Using Microsoft Excel 2002 ISBN: 0-7897-2511-8, also from Que.