SQL stored procedure with paramters from MS Query

[JimF]

Is it possible to call a SQL stored procedure with parameters that come from
a worksheet cell using MS Query?

 

[Carlos Antenna]

Have a look at this page by Nick Hodge. I think this may be what you're
looking for.

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

-- Carlos

 

[Jamie Collins]

Is it possible to call a SQL stored procedure with parameters that come from
a worksheet cell using MS Query?

Not automatically using MSQuery's own parameters feature.

To be able to use MSQuery parameters, the SQL to be a simple SELECT
written in MSQuery's own SQL dialect, rather than that odbc syntax or
the dialect of the database server. Using ocdb's CALL syntax, ANSI's
EXECUTE syntax or anything proprietary will result in the 'cannot
display graphically' (whatever that means) message, after which support
for parameters is lost.

You can, however, write VBA to dynamically change the querytable's SQL
text using values from a worksheet.

Jamie.

--

 

[JimF]

Carlos,

Thanks for your help. The problem is the Query Wizard only allows you to
select tables or views. It does not let you select SQL stored procedures.
You can cancel out of the wizard and then click the SQL button to enter an
EXEC stored_producer command. When you accept the SQL it does some error
checking and makes sure you have supplied the same number of parameters as
the stored procedure is expecting.

The problem is I don't know how to use a cell reference for one of the
parameters.

Thanks,
Jim

 

[JimF]

Thanks Jamie,

It's good to know VBA can modify the querytable's SQL although I was tring
to avoid VBA.

Have you used SQL.REQUEST?

See my problems with it in the topic "problems with SQL.REQUEST"

Jim