Assign Values to an ADODB parameter

[jon_sg]

Can anyone help

I have a stored parameter query that I use as with an ADODB command object to return an ADODB Recordset.
There is alot of SQL in the query so I don't want to use an SQL statement in the code .

As this function is used by several forms I cannot set the Parameters as form control references in the SQL I can't use an eval function to set them. The SQL parameters are [Param1] and [Param2]

I can't seem to get the syntax right to assign the parameter values to the variables in the function

does any one know how to do this

Thanks for your help

Regards

Jon

 

[Rich]

Search here for QueryDefs and passing parameters, examples have been posted

 

[jon_sg]

ADO Parameter Syntax

Thanks

I had tried searcing prior to posting and couldn't get the examples to work with ADO as all the examples here are for DAO I have posted the bones of the ADO code I used.

I eventually found a side note ref to ADO Syntax in the Desktop Dev Handbook 2002.

For those who may be interested for future ref
The ADO syntax has to be ("[Parameter]")
unlike DAO where you can use ![Parameter] also with ADO the [] must be included.

Query Defs don't exist in ADO so I used the Parameters collection of the ADO command object.

This seems to work pretty efficiently on large data tables as it minimises the recordset returned.

Here is the bare bones of the code:

Public Function GetXrate(strSrcCur As String, strAccCur As String) As Double
'Calculates the XRate value for two currencies
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn

With cmd
.CommandText = "qryGetXrate"
.CommandType = adCmdTable
.Parameters.Refresh
.Parameters("[Param1]") = strSrcCur
.Parameters("[Param2]") = strAccCur
End With

Set rst = cmd.Execute
' + code
GetXrate = rst!fldXrate
End Function

Apparantly it is best to use (" ") rather than ! operator when refering to instances in collections in ADO as it can be applied consistantly.

(Forgive me if I made inaccurate refs to DAO I have never used it)

Regards

Jon