Passing Parameters to Access 97 Database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Passing Parameters to Access 97 Database

  1. #1
    Robert Boudra Guest

    Passing Parameters to Access 97 Database

    I am using VB6 SP4 and ADO 2.5 to retrive and update data in an Access 97
    database. In the past I have used the Recordset object exclusively against
    the connection object to return recordsets. However, recently, I started
    trying to make use of the Command object so that I can pass Parameters to an
    existing Access Query which contains Parameters. However, after spending a
    significant amount of time and effort, I don't seem to be able to get this
    to work. Basically, the process I was using was to create a Command object
    against an open Connection object, then set the CommandText and
    Parameters("AccessParamater") properties. I would then Set a Recordset
    Object equal to the Command object. Each time, I would get an error while
    trying to set the Parameters("ParameterName").Value equal to a text string
    saying that it couldn't find the Parameter name in the Parameters
    collection. I know that there is some fundamental mistake I'm making, but I
    can't seem to figure it out. Does someone have some example code that could
    get me started?

    Bob




  2. #2
    Paul Clement Guest

    Re: Passing Parameters to Access 97 Database

    On Sun, 17 Dec 2000 19:16:17 -0700, "Robert Boudra" <R_BOUDRA@prodigy.net> wrote:

    I am using VB6 SP4 and ADO 2.5 to retrive and update data in an Access 97
    database. In the past I have used the Recordset object exclusively against
    the connection object to return recordsets. However, recently, I started
    trying to make use of the Command object so that I can pass Parameters to an
    existing Access Query which contains Parameters. However, after spending a
    significant amount of time and effort, I don't seem to be able to get this
    to work. Basically, the process I was using was to create a Command object
    against an open Connection object, then set the CommandText and
    Parameters("AccessParamater") properties. I would then Set a Recordset
    Object equal to the Command object. Each time, I would get an error while
    trying to set the Parameters("ParameterName").Value equal to a text string
    saying that it couldn't find the Parameter name in the Parameters
    collection. I know that there is some fundamental mistake I'm making, but I
    can't seem to figure it out. Does someone have some example code that could
    get me started?

    This would be a lot easier to troubleshoot if you could post your code.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  3. #3
    Martin Guest

    Re: Passing Parameters to Access 97 Database


    Hi Bob

    here is an example that works
    ------------------------------

    Dim cmd As New ADODB.Command
    Dim rs as New ADODB.Recordset

    Dim sQueryName as String
    Dim lParam1 as long
    Dim sParam2 as String

    lParam1 = 100
    sParam2 = "Miller"

    sQueryName = "QueryInAccess97"

    With cmd
    Set .ActiveConnection = con 'con is an active ADODB.Connection
    .CommandText = sQueryName
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("Param1", adInteger, adParamInput, ,
    lParam1)
    .Parameters.Append .CreateParameter("Param2", adVarChar, adParamInput, len(sParam2),
    sParam2)
    End With

    rs.Open cmd, , adOpenKeyset, adLockOptimistic

    DEFINITION of the query "QueryInAccess97":

    PARAMETERS Param1 long, Param2 text;
    SELECT FROM Table1 WHERE Table1.CustomerID =[Param1] AND Table1.Name = [Param2]

    1) You must create the parameters in the same order as they are defined in
    your query.(Same type and name aswell of course)
    2) if you use a parameter of type 'adVarchar' its length must be passed aswell.



  4. #4
    Robert A. Boudra Guest

    Re: Passing Parameters to Access 97 Database

    Thanks for your help. Got it working now.

    Bob

    Martin <mkvasnicka@dtc.ch> wrote in message news:3a3f1642@news.devx.com...
    >
    > Hi Bob
    >
    > here is an example that works
    > ------------------------------
    >
    > Dim cmd As New ADODB.Command
    > Dim rs as New ADODB.Recordset
    >
    > Dim sQueryName as String
    > Dim lParam1 as long
    > Dim sParam2 as String
    >
    > lParam1 = 100
    > sParam2 = "Miller"
    >
    > sQueryName = "QueryInAccess97"
    >
    > With cmd
    > Set .ActiveConnection = con 'con is an active ADODB.Connection
    > .CommandText = sQueryName
    > .CommandType = adCmdStoredProc
    > .Parameters.Append .CreateParameter("Param1", adInteger, adParamInput, ,
    > lParam1)
    > .Parameters.Append .CreateParameter("Param2", adVarChar, adParamInput,

    len(sParam2),
    > sParam2)
    > End With
    >
    > rs.Open cmd, , adOpenKeyset, adLockOptimistic
    >
    > DEFINITION of the query "QueryInAccess97":
    >
    > PARAMETERS Param1 long, Param2 text;
    > SELECT FROM Table1 WHERE Table1.CustomerID =[Param1] AND Table1.Name =

    [Param2]
    >
    > 1) You must create the parameters in the same order as they are defined in
    > your query.(Same type and name aswell of course)
    > 2) if you use a parameter of type 'adVarchar' its length must be passed

    aswell.
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center