Using the Excel Query Program

---

Here's a short example that uses the Excel Query Program to retrieve a list of customers from the Northwind sample database that is installed with Microsoft Access. This database is usually kept in the OFFICE11\SAMPLES directory, which might look like the following path if you installed Microsoft Office in its default location:

c:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb

Note 

If you can't find the Northwind sample database in the Microsoft Office\OFFICE11 \SAMPLES directory, verify that you installed the sample databases for Access by rerunning the Office 11 Setup program.

Configure the Connection Information

After loading the sample workbook, press the Database button and select Access (Jet) in the drop-down box at the top of the Database Properties window. (See Figure 24-6.) Then enter the full path to the file containing the database and press OK.

Figure 24-6: Choose Access (Jet) in the drop-down box, and then enter the path in the database.

Enter a Query

Next press the Edit Query button, enter the following query (shown in Figure 24-7), and press the Close button.

Select *
From Customers
Order By CustomerId

Figure 24-7: Enter the query you want to run.

Run the Query

Press the Run Query button to retrieve the information from the database and display it in the current worksheet (see Figure 24-8).

Figure 24-8: View the results from the database query as a normal worksheet.

In this chapter, you learned how to combine database programming with user forms and command bars to create a useful application that runs in Excel. The program began by loading a command bar containing the controls that operate the application. Pressing the Database button displays a form where the user can enter the information necessary to connect to the database. Pressing the Edit Query button displays another user form that displays the current database query and allows the user to change it. Finally, pressing the Run Query button runs the query using calls to ADO and displays the results in the currently active worksheet.

---