Most of our user base accesses corporate data by creating ODBC queries in Excel 2003 using Microsoft Query. For more complex things, they often attract me to participate.
There were several cases when I decided that the most logical way to extract certain data would be to use the Inline Table-Valued function to achieve the functionality of a parameterized view. This works fine when called from Query Analyzer:
SELECT * FROM fn_AverageRecovery('2009-07','2009-10')
Sequence Process Centre Process Centres_Description Input Qty Output Qty Recovery
10 GM Green Mill 12345.678 11223.344 11
11 LYR Log Yard Report 98765.432 55443.322 99
20 MB MultiBand Resaw 5555.666 5555.444 50
However, entering the same SELECT clause in MS Query causes an error: the table 'fn_AverageRecovery (' 2009-07 '' could not be added.
Not only that, but what I really want to do is have the period parameters obtained from the spreadsheet, however, if I replace the literals with question marks, then MS Query gives me a terrible error: the parameters are not allowed in queries that can be displayed graphically .
Now I managed to get around this situation sometimes in the past, using the rather inelegant method of entering some simple SQL statement, by clicking any cell in the result set in Excel, then switching to VBA and setting the CommandText property manually in the Immediate window:
ActiveCell.QueryTable.CommandText="select * from fn_AverageRecovery(?,?)"
No complaints from VBA. But when I return to Excel and right-click on the cell and select "Refresh Data", I get two errors:
[Microsoft] [SQL Server ODBC driver] Invalid parameter number
[Microsoft] [SQL Server ODBC driver] Invalid handle index
, , , , -. , - .