Query Access DB from Excel 2007 by "Microsoft Query"

Overview

Let's consider this scenario: I have a Access DB and want to retrieve the data in Excel. In Excel 2007, the easiest way to do it (as far as I know) is to use built-in Access connect ("Data" tab => "Get External Data" group => "From Access").

However, in this way I cannot pass parameter into the query, how about if I want to use the value of a cell as the query parameter? In this case, you can use "Microsoft Query" in Excel 2007.

Seems that "Microsoft Query" can be used on other DB, but I haven't tried it out yet.

Reference

1. Connect to (Import) external data (Excel 2007)
2. Use Microsoft Query to retrieve external data
3. Customize a parameter query

Steps

Build a parameter query with "Microsoft Query"

1. In Excel 2007, select "Data" tab => "Get External Data" group => "From Other Sources" => "From Microsoft Query"
2. Select your Access database
3. In the "Query Wizard", setup your query and select "View data or edit query in Microsoft Query" to open "Microsoft Query"
4. In "Microsoft Query", click the "Show/Hide Criteria" button (the button with a pair of glasses on it)
5. Select the Criteria Field (Field to be compared in SQL 'WHERE' clause), and enter "[Parameter Name]" in "Value" to specify a parameter in the SQL query.
6. Switch to SQL view and you should see a 'WHERE' clause like this:
   WHERE customer_id = ?
   The question mark "?" represents a parameter.
7. Go ahead and modify the SQL as you like. When finished, select "File" => "Return Data to Microsoft Office Excel" to save and exit.

Use a cell's value as parameter (For more detail, go to Reference 3)

1. Click on any cell of your query result
2. Select "Data" tab => "Connections" group => "Properties" (If "Properties" button is grayed out, redo Step 1 )
3. Click "Connection properties" (The little button next to Connection Name)
4. Select "Definition" tab, click "Parameters" (If there is no "Parameters" button, it means that your query doesn't contain parameter, redo Step 1-7 in previous section)
5. Select the desired parameter, click "Get the value from the following cell:" and select the cell, you may also select "Refresh automatically when cell value changes"
6. Done. Change the value of the cell to test the query.
7. Happy building query! (Hopefully...)