Use a stored procedure in Access 2007 ..

[krishnak]

Hi All,
I have a test database on the SQL 2008 Express and an Access application (.accdb) linking to the project as the front end. I want to generate a query that gives me the results of calculated values of the fields in the table in the SQL database. I wrote some functions for the formulas in VBA in Access and used an SQL query in Access to retrieve the result as a query in Access. No problem in that.
I creates some user functions in the SQL server for the same formulas and created a stored procedure for the same results. I want to make use of the stored procedure in the SQL query in Access. I tried to use the RunSQL action query and used the statement:
EXECUTE sp_GetMetrics.
That does not work. I tried a simple statement like retrieving all details from a table (not a stored procedure).
SELECT * FROM dbo_Metrics.
This does not work.
The message says that I should use CREATE , PROCEDURE, SELECT ..INTO statements.
What is the correct way to do this?
Do I create an empty table first in Access and then use SELECT .. INTO?
If I use PROCEDURE, the message says that I am using wrong syntax for the parameters. But I do not have any parameters for the stored procedure.
Will someone please suggest how I go about it?
The Access application is linked to the SQL database - if I have to write a new ADO script, do I have to explicitly specify the connections string and server again?
I am searching in the internet for some help, but I am not able to make out much concerning my problem from the tons of info available in the internet.
Thanks in advance.

[stanl]

In my experience with SQL Server and Access (2003, but should be same for 2007) executing stored procedures is best accomplished with the Command Object, viz. oCmd = CreateObject("ADODB.Command"). It is also possible to simulate a stored proc in Access (it will not display in the queries but can execute via ADO). If you could post a little more detail about what you are trying to accomplish perhaps myself or someone else and point out the Command Object code.