Passing parameter to a query in VBA (ADO)

  • Thread starter Thread starter Chuck
  • Start date Start date
C

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
 
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
 
Thanks

Gerald Stanley said:
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
Click to expand...
 
Back
Top