referring to a stored querydef that requires a parameter

[SunWuKung]

Hi,
For some obscure reason I need to use ADO.

As I learned, I can execute a stored querydef with a parameter in ADO like this:
cmd.CommandText = "[InstrumentDeleteEmptyQuads]"
Set rst = cmd.Execute(, Array(CurrentInstrumentID), adCmdStoredProc)

So far, so good...

But when I have a SQL statement in VBA that refers to a stored querydef that requires a parameter, I have no ide how to pass that parameter, since I am not executing that stored querydef directly from VBA, only the sql string that I execute refers to it.

Should I try to pass the parameter to the Sql string that I execute - how to do that? -, or to the stored querydef without executing it - again, how to do that?

Thanks for any help.
SWK

 

[RichMorrison]

The basics for parameters with ADO commands:


Dim pmDateFrom As New ADODB.Parameter

Set pmDateFrom = ADO_Command.CreateParameter("ParamName", adDBDate, adParamInput)

pmDateFrom.Value = DateFrom

ADO_Command.Parameters.Append pmDateFrom

HTH,
RichM

 

[SunWuKung]

Now I suceeded to append my 3 parameters to the query with the code below. I can execute the query with
Set rst = cmd.Execute

but how would I open a form that has my query as its recordsource withouth Access asking for the parameters?

(I tried a simple DoCmd.OpenForm "FormName" but although its recordsource is InstrumDerivedScaleStats3 for which I already supplied the parameters it still asks for the parameters.)

Any ideas?
Thanks for the help.
SWK


Dim prm As New ADODB.Parameter

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "InstrumDerivedScaleStats3"
.CommandType = adCmdStoredProc

'create a parameter NormHeaderID and then append it
Set prm = .CreateParameter("NormHeaderID", _
adDate, adParamInput, , NormHeaderID)
.Parameters.Append prm
'create a parameter DerivedInstrumHeaderID and then append it
Set prm = .CreateParameter("DerivedInstrumHeaderID", _
adDate, adParamInput, , DerivedInstrumHeaderID)
.Parameters.Append prm
'create a parameter OrigInstrumHeaderID and then append it
Set prm = .CreateParameter("OrigInstrumHeaderID", _
adDate, adParamInput, , OrigInstrumHeaderID)
.Parameters.Append prm
End With

 

[SunWuKung]

I finally found the solution. I paste it here hoping it might help someone.
The code below sets the 3 parameters of the InstrumDerivedScaleStats3 stored query and set it as the recordsource of a form.

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter

Set rst = New Recordset
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic

Set cmd = New Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "InstrumDerivedScaleStats3"

Set prm = cmd.CreateParameter
prm.Type = adInteger
'prm.Size = 25
cmd.Parameters.Append prm
cmd.Parameters(0).Value = NormHeaderID

Set prm = cmd.CreateParameter
prm.Type = adInteger
cmd.Parameters.Append prm
cmd.Parameters(1).Value = DerivedInstrumHeaderID

Set prm = cmd.CreateParameter
prm.Type = adInteger
cmd.Parameters.Append prm
cmd.Parameters(2).Value = OrigInstrumHeaderID

rst.Open cmd

DoCmd.OpenForm "DerivedInstrumScaleStats"
Set Forms("DerivedInstrumScaleStats").Recordset = rst

--------------------------------------
SWK