Displaying the results of a sp with parameters

[mmitchell]

Is there a way to run a sp after passing it parameters and having it display the results.

I am using an .ADP and MS SQL2000

I can do this to run a sp with parmeters but not display the results:

CurrentProject.Connection.Execute ("spDataValidation_Scrap_GoodVsScrapPerHr @BeginDate=" Me.begindate)


And I can do this to run a sp and display the results, but not pass it any parameters:

DoCmd.OpenStoredProcedure "spDataValidation_Scrap_GoodVsScrapPerHr"

Is there an "in-between" that allows me to do this in "one statement call." I say that because I know I can create the sql string in code and have the variables in it then set a form to that and open the form, but all I want to do is have the sp run and display the results (in as litttle code as possible)

 

[adam_fleck]

A way that might work

In Form Design View:

Set the form's RecordSource property (the Data tab on the properties popup) to the name of the stored procedure you're wanting use:

spDataValidation_Scrap_GoodVsScrapPerHr

Set the form's InputParameters Property to a string similar to this:

@BeginDate char='2004-08-11'

Or, In VB:

    Me.RecordSource = "spDataValidation_Scrap_GoodVsScrapPerHr"

    Me.InputParameters = "@BeginDate char='" & _
                          format$(Me.BeginDate,"yyyy-mm-dd") & "'"

Comment

I've become a bit leery of dates, and always set stored procedures to take parameters of type nvarchar(32) which the stored procedure then converts to date values.

To get a stored procedure to convert a string in the form '2004-08-11' to a date value use SQL stored procedure syntax:

convert(datetime, @BeginDate, 20)

Hopefully, someone will talk me out of this date paranoia.

Alternatives?

It's also possible to set a form's recordset to a recordset opened through a stored procedure. There are probably several ways of doing this, though I know of only one. If the above doesn't work, or isn't usable, then there's a relevant post somewhere on this forum which I'll try to find.

 

[mmitchell]

Thanks, but I guess I did not specify enough that I am not trying to set a form's recordsource. All I want to do is open the the sp in datasheet view, like running an MDB query by double clicking it and entering the parameters in the prompts that pop up. Using code that is, instead of doubleclicking it.

 

[adam_fleck]

The way I'd do what I think you're wanting to do is, I'd have a frame form with a box for @BeginDate. The recordsource returned by your stored procedure would be displayed in a subform of this in datasheet view (and with a little adaptation, the code suggested should work).

Apart from the frame form, it would look pretty much as if you'd opened your stored procedure naked as it were, although it would still be decently clothed by a form.

One of the principles I work to is that users never get to see data except through a form. This needn't be a lot of extra work though. When the recordset you need is showing on screen, you can use the Access autoform button to set up a form for you.