Excel 2010 - MS Query - Error when using parameter in a subquery [SOLVED]

[SalimJ]

Hi All,


I have a worksheet where I "Get External Data" from SQL Server via MS Query.


I have the following SQL statement :


SELECT *

FROM TransactionFile

WHERE Dept IN (SELECT Dept FROM UserDeptFile WHERE UserCode = 'UserCode')


It works fine.


However, when I replace the 'UserCode' with ? in the subquery, Excel gives me the 2 following error messages:


[Microsoft][ODBC SQL Server Driver] Syntax Error or access violation


[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index


Can anyone help me please ?


Thanks.

 

[NARAYANK991]

Hi Salim ,


In MS Query SQL , aren't the underscore "_" and the percent sign "%" the wildcards ?


Narayan

 

[SalimJ]

Hi Narayan,


Thanks for your quick reply, but I don't really understand your question.


I don't need "_" or "%". I just want to prompt the user to enter his usercode and pass the code to the query.

 

[Colin Legg]

Try adding square brackets like this:

[pre]

SELECT *
FROM TransactionFile
WHERE Dept IN (SELECT Dept FROM UserDeptFile WHERE [UserCode = ?])[/pre]
Or if you're using the query design grid then put something like [code][Enter UserCode] in the [code]Dept

Value field of the UserDeptFile[/code] table (not TransactionFile[/code]).


If you want to be able to type this parameter into a cell then more work needs to be done.

 

[SalimJ]

Hi Colin,


I have already tried the square brackets as well as referencing the parameter in a cell.


I still get the error message. For info, the ? works fine in a direct WHERE clause. The problem is in subquery.

 

[NARAYANK991]

Hi Salim ,


Sorry , my mistake ; the following links seem to suggest that this is a bug :


http://www.mrexcel.com/forum/excel-questions/429136-invalid-parameter-number-error.html


http://social.msdn.microsoft.com/Forums/sqlserver/en-US/99e25a59-9df7-41f1-9032-3fe454326d32/sqldescribeparam-with-subselect-invalid-parameter-numberinvalid-descriptor-index


Narayan

 

[SalimJ]

Oops ! I will have to find another way to deal with this issue.


May be through VBA that I have never used before; I am not a programmer.


Any help will be much appreciated.

 

[NARAYANK991]

Hi Salim ,


This link has a suggested work-around :


http://stackoverflow.com/questions/15249704/how-to-use-this-query-in-excel-is-the-query-wrong-or-just-excel-not-powerful-en


Unfortunately , you can't escape from VBA.


Narayan

 

[Colin Legg]

Hi,

I didn't know about this limitation either. I haven't tested this but, if you have permissions on the SQL Server database, you could create a View called

vYourView (give it a more appropriate name) along the lines of:

[pre][code]SELECT TransactionFile.*, UserDeptFile.UserCode
FROM TransactionFile
INNER JOIN UserDeptFile ON TransactionFile.Dept = UserDeptFile.Dept

And then you could query the view from MS Query using:

SELECT vYourView.*
FROM vYourView
WHERE vYourView.UserCode = 'UserCode'[/code][/pre]

 

[SalimJ]

Narayan,


Many thanks for the link. Unfortunately, I really don't understand anything that is written there. I do have some basics of programming, but this one is too difficult for me to understand.


I will try the view option. What I'm scared is that there are many records in the TransactionFile. I don't know how it will behave.


I'll keep you updated.


Many thanks again.


Salim.

 

[jeffreyweir]

Or you could use a stored procedure. Some good info on how to do that at http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

 

[SalimJ]

Hi All,


Finally I've inspired myself with what jeffreyweir has suggested. Instead of Stored procedure, I replace the whole SQL text using the following:


With ActiveWorkbook.Connections("MYSERVER").ODBCConnection

.CommandText = "The SQL Command"

End With

Sheet1.Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False


It works fine.


Thanks to all of you for your support.


Salim.