excel - Using Parameters in SQL query with sub-query

Question

I have a fairly complicated SQL query with a nested subquery. When I try to use parameters in Microsoft Query is say I can use parameters in queries that cant be represented graphically. So I need another option. I think you can place your SQL query in a cell as a string then have a Macro run it. Any ideas how I could do this?

Thanks

-Jesse

Answer 1

Here's what I do to work around the limitations of Microsoft Query in Excel 2007:

1. A produce a dummy query (SELECT NULL AS Test, for example) in Microsoft Query and insert it into the worksheet.
2. Right-click on the table that MS Query just inserted and click Table->Edit External Data Properties.
3. Click on the Connection Properties button, then click the Definition tab.
4. In the Command Text section, write out or paste in the query that you want, using the usual '?' convention for parameters, then click OK.
5. Click OK to exit the External Data Properties window.
6. Right click on the table again, and select Table->Parameters to bind the parameters in the usual way.

The idea is the bypass the GUI that MS Query provides, which has some arbitrary limitations that the underlying engine does not.

This works for many complex queries, but not all. When I encounter a query that MS Query refuses to digest at all, I either refactor the query (when feasible) or create a VIEW on the SQL server and query against that.