Open parameterised stored proc as a resultset in a window

  • Thread starter Thread starter sebt
  • Start date Start date
S

sebt

Hi

not very familiar with ADP, so I'm not sure I can do this.

Double-click on a stored proc in the Stored Procs view in Access. It
prompts me for two parameters. As expected. Then it opens the
resultset in a normal Access "table/query view data" window.

How can I do exactly this in VBA, but providing the parameters so that
the user doesn't get prompted for the parameters?

Back in.... oh, was it Access97? I could do something like

(approximately)
Dim qdef AS Querydef
Set Qdef=CreateQueryDef
With qdef
.SQL="EXEC storedprocedurename @param1=2005,@param2=1"
.Open
End With

or something like that. Or maybe it was

Set qdef=Querydefs("An existing parameterised query")
With qdef
.Parameters(1).Value=2005
.Parameters(2).value=1
.Open
End With

But Querydefs are now gone. Stored procedures are now something nasty
called an AccessObject. And whatever the documentation says, the
Properties collection of AccessObjects just doesn't exist - gives me a
"the expression you entered refers to an objet which is closed or
doesn't exist" error whenever I try to get at it.

OK fair enough an SP is a SQL Server object, not an Access object. But
there must be some interface which allows Access to know that the SP
wants parameters, otherwise how would it flip up those parameter entry
Inputboxes? So surely there must be some way to specify the parameter
values in code in advance?

I don't want to set up some form and change my SP parameter names to
some Access control name like Forms!AForm.AControl.Value. IMHO that
kind of thing doesn't belong in a SQL Server database.
I could open up the SP in ADO and squirt the results out to a CSV or
Excel file, but this seems like much too much bother.

thanks for any ideas


Seb
 
Personally, I never tried to open a SP directly to see its resultset without
using a form and I don't understand why you don't want to use a form.

If this because you are new to ADP and don't really know where to go or
because you are allergic to forms for some unknown reason?

For passing parameters from a form to a SP, you may use the InputParameters
property:

@param1 int = 2005, @param2 int = Forms!AForm!AControl

If I remember correctly, you can also set the the name of a control to the
name of a parameter; however, I had a very bad time with this method on
Acc2000 and since then, I never tried to use this syntax again.
 
Personally, I never tried to open a SP directly to see its resultset without
using a form and I don't understand why you don't want to use a form.
Click to expand...

Aha! I didn't want to use a form because this result-set is read-only,
and only shown so that the user can copy/paste it out of the system.

Forms shown as forms are not much good for this. But of course a form
can look like a datasheet. Out of habit I've never used forms in
Datasheet view; somehow in 10 years developing Access I've never found
a need for them. Now, in ADP, with no queries, here is a purpose for
which they're ideal.

thanks for the tip!
 
Back
Top