Return parameter with Pass-Through query

[khurram7x]

Hi,

How could I get the return value from back-end SQL Server procedure?? How should I call the procedure using DAO to achieve this please?

Regards,
K

 

[Minty]

Set up a pass through query. In it run your stored procedure. Make sure you set the query properties to return results = yes. In design view it will look something like

EXEC dbo.yourSPName @iYourParameter = 12345

This would assume that you have a parameter that is an integer. It always appears that it's important to know the parameter names and call them the same things.

This will get you the SQL SP results into a query window.

If you need to dynamically change the parameters then you will need to use a query def something like;

Set db = CurrentDb
        Set qdfpt = db.QueryDefs("yourPassThroughQuery")
        qdfpt.Connect = "ODBC;DRIVER=SQL Server;SERVER=SQLSERVER;;DATABASE=YourSqlDatabase;UseTrustedConnection=True"
        qdfpt.SQL = "exec dbo.YourStoredProcedure @YourParameter = " & Me.YourParameterOnTheForm & ""

        db.Close

 

[khurram7x]

I called a PT function and got the results back in query.

Call RunPassThroughSELECT("declare @ID1 varchar(20) EXEC pc.acc @ID1 OUTPUT select @ID1")