Passing parameter to a query in VBA (ADO)

[Chuck]

I need to pass two parameters to a query using ADO. The only way I could
get it to work is by using the code below where "Set prm =
cmd.CreateParameter" is used once for each parameter. I'm not sure if this
is necessary so any insight would be appreciated. Thanks

Set rst= New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cmd = New Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = My_SQL_String

Set prm = cmd.CreateParameter
prm.Type = adChar
prm.Size = 25
cmd.Parameters.Append prm
cmd.Parameters(0).Value = Me!My_Control_1

Set prm = cmd.CreateParameter
prm.Type = adInteger
prm.Size = 5
cmd.Parameters.Append prm
cmd.Parameters(1).Value = Me!My_Control_2

rst.Open cmd

 

[Gerald Stanley]

If the commandtext uses the PARAMETERS syntax, then the
number of parameter objects in the parameters collection
will be created automatically. Thus the code can be reduced to

Set rst= New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = My_SQL_String
cmd.Parameters(0).Value = Me!My_Control_1
cmd.Parameters(1).Value = Me!My_Control_2
rst.Open cmd

Hope This Helps
Gerald Stanley MCSD

 

[Chuck]

Thanks

If the commandtext uses the PARAMETERS syntax, then the
number of parameter objects in the parameters collection
will be created automatically. Thus the code can be reduced to

Set rst= New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = My_SQL_String
cmd.Parameters(0).Value = Me!My_Control_1
cmd.Parameters(1).Value = Me!My_Control_2
rst.Open cmd

Hope This Helps
Gerald Stanley MCSD