Executing Stored Procedures in Access 2000 VBA

Executing Stored Procedures in Access 2000 VBA

Post by Tony Wilhel » Mon, 16 Jul 2001 05:13:31



I am trying to execute a stored procedure in SQL 7.0 database and I'm
getting the following error:

supplied.

Below is my code:
        adocmd.ActiveConnection = CurrentProject.Connection
        adocmd.CommandType = adCmdStoredProc
        adocmd.CommandText = "sp_AddItems"
        adocmd.Parameters(1) = Me.cboAreaID
        adocmd.Parameters(2) = Me.txtNewItem
        adocmd.Execute

any suggestions.

Thanks
Tony

 
 
 

Executing Stored Procedures in Access 2000 VBA

Post by BurtonRobert » Mon, 16 Jul 2001 11:39:12


Try:
Dim prm as adodb.parameter
With adocmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "sp_AddItems"

Direction:=adParamInput, , Value:=Me.cboAreaID)
    .Parameters.Append prm

Direction:=adParamInput, Size:=50, Value:=Me.txtNewItem)
    .Parameters.Append prm
    .execute
End With

This assumes I've guessed right on the datatypes of your parameters in SQL
Server.

A cool shortcut to this, if you have ADO 2.6 (and, maybe, ADO 2.5, too), is
to execute your sproc as a method of the connection:

Dim cnn as adodb.connection
Set cnn = currentproject.connection
cnn.sp_AddItems Me.cboAreaID, Me.txtNewItem

Your way may work, too, if you use Zero for the index of your first
parameter instead of 1.

HTH
Burton Roberts


> I am trying to execute a stored procedure in SQL 7.0 database and I'm
> getting the following error:

> supplied.

> Below is my code:
>         adocmd.ActiveConnection = CurrentProject.Connection
>         adocmd.CommandType = adCmdStoredProc
>         adocmd.CommandText = "sp_AddItems"
>         adocmd.Parameters(1) = Me.cboAreaID
>         adocmd.Parameters(2) = Me.txtNewItem
>         adocmd.Execute

> any suggestions.

> Thanks
> Tony


 
 
 

Executing Stored Procedures in Access 2000 VBA

Post by Tony Wilhel » Tue, 17 Jul 2001 22:50:13


Thanks Burton

I guess that this is just a shortcoming of Access VBA (my code from the
first message works in VB and VBS).

Using 0 for the index of the first parameter index should be the return
parameter from the stored procedure.

Thanks
Tony Wilhelm


> Try:
> Dim prm as adodb.parameter
> With adocmd
>     .ActiveConnection = CurrentProject.Connection
>     .CommandType = adCmdStoredProc
>     .CommandText = "sp_AddItems"

> Direction:=adParamInput, , Value:=Me.cboAreaID)
>     .Parameters.Append prm

> Direction:=adParamInput, Size:=50, Value:=Me.txtNewItem)
>     .Parameters.Append prm
>     .execute
> End With

> This assumes I've guessed right on the datatypes of your parameters in SQL
> Server.

> A cool shortcut to this, if you have ADO 2.6 (and, maybe, ADO 2.5, too),
is
> to execute your sproc as a method of the connection:

> Dim cnn as adodb.connection
> Set cnn = currentproject.connection
> cnn.sp_AddItems Me.cboAreaID, Me.txtNewItem

> Your way may work, too, if you use Zero for the index of your first
> parameter instead of 1.

> HTH
> Burton Roberts



> > I am trying to execute a stored procedure in SQL 7.0 database and I'm
> > getting the following error:

> > supplied.

> > Below is my code:
> >         adocmd.ActiveConnection = CurrentProject.Connection
> >         adocmd.CommandType = adCmdStoredProc
> >         adocmd.CommandText = "sp_AddItems"
> >         adocmd.Parameters(1) = Me.cboAreaID
> >         adocmd.Parameters(2) = Me.txtNewItem
> >         adocmd.Execute

> > any suggestions.

> > Thanks
> > Tony