Passing Parameters to Access 97 Database

[Robert Boudra]

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

[Paul Clement]

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)

[Martin]

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.

[Robert A. Boudra]

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