Passing parameter to a query in VBA (ADO)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Chuck (no.address_at_no.where)
Date: 04/06/04 

Date: Tue, 6 Apr 2004 08:36:35 -0400

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

Relevant Pages

  • RE: ODBC query in VB code Need HELP
    ... Everything for ADO is in the first 2 messages that I gave you. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: Connecting to Query
    ... practice examples of the ADO Command, Parameters, etc. you explained below. ... Second, with the selected territory ... >> combo box to select a product from a query based primarily on a link ...
    (microsoft.public.access.formscoding)
  • Re: On ADSI and LDAP
    ... the problem is how can I retrieve the value for myuser using the ... would be more efficient to use ADO to query AD for the attributes values. ... For more on using ADO, ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes ...
    (microsoft.public.scripting.vbscript)
  • Re: cant get query to run over ADO, but works fine in Access inte
    ... Thanks for the suggestions, Richard. ... the query so that unchanging parts of it are stored as a query with the part ... As I've had trouble with synchronizing ADO ... > Execute method of the ADO Connection object, but the issue is the same - you ...
    (microsoft.public.data.ado)
  • Re: Stored Procedure is running slow!
    ... > I run stored procedure from QA, it takes some 15 seconds to execute. ... > seconds when called from MS Fox Pro, via ADO. ... Try this in Query Analyzer: ... By default Query Analyzer runs with ARITHABORT ON, ...
    (microsoft.public.sqlserver.programming)