krishnak
05-17-2010, 06:21 AM
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.
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.