MS Access to invoke stored procedure that's located on an SQL Server

[jadn]

Hello!
Is it possible for MS Access to invoke a stored procedure which is located on an SQL Server?

The following MS Access query returns the "Tests" table (located on the SQL Server):

SELECT *
FROM [ODBC;Driver={SQL Server};Server=111.111.111.111;Database=MYDB;UID=MYUID;PWD=MYPW;].Tests;

but THIS query

SELECT *
FROM [ODBC;Driver={SQL Server};Server=111.111.111.111;Database=MYDB;UID=MYUID;PWD=MYPW;].MyStoredProc;

Results in
"The Microsoft Jet database engine cannot find the input table or query..."

MyStoredProc is not a table, it's a stored procedure.
Might this be a SQL Server permissions issue? MYUID is a dbReader with execute permission over MyStoredProc...

This seems like an SQL Server question too, is it kosher to post twice - once in each forum?

Any help is gratefully received,
Cheers.

 

[DotNetGnat]

you have to do something like this:

Exec [ODBC;Driver={SQL Server};Server=111.111.111.111;Database=MYDB;UID=MYUID;PWD=MYPW;].MyStoredProc

-DNG

 

[jadn]

Hi DNG,
Thanks for the reply, but it's the "something like" part I'm a little fuzzy on. ;^)

I tried your [TSQL?] "EXEC [ODBC; ..."
and received the error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE','SELECT', or 'UPDATE'"

It seems MS Access queries are very strict/limited in syntax.

Cheers!

 

[jadn]

follow-up:
Looking at DNGs reply (again) made me think "If only I could run this command on the remote server"...

...well I'm sure the answer is obvious to most of you Acces gurus, but I guess you didn't notice this thread or feel like replying...

I found joy following the MSAccess help for "Pass-through query".
BTW, the connection-string looked like this:
ODBC;Driver={SQL Server};Server=?.?.?.?;Database=DBName;UID=UserName;PWD=Password;

Cheers!