Stored Procedures

[qqqq]

Hi

Please can someone help?

I am trying to call a paramaterised stored procedure from VBA in a ADP.

All the info I can find on the net use ADO to make a connection, but surely
in a ADP u r already connected

Please can anyone assist.

Cheers

Geoff

 

[Daran Johnson]

Try the following:

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "dbo.MyStoredProcedure"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute

 

[Eva Etxebeste]

Hello Geof

You can use :

Set spMyProcedure = New ADODB.Command
With spMyProcedure
.CommandType = adCmdStoredProc
.CommandText = "spa_Proc"
Set .ActiveConnection = CurrentProject.Connection
.Parameters.Append .CreateParameter("@Param1",
adVarChar,adParamInput, 10)
.Parameters.Append .CreateParameter("@Param2",
adVarChar,adParamInput, 10)
.Parameters.Append .CreateParameter("@Param3",
adInteger,adParamInput)

.Parameters("@Param1") = strParam1
.Parameters("@Param2") = strParam2
.Parameters("@Param3") = intParam3

.Execute
End With

Or

Set spMyProcedure = New ADODB.Command
With spMyProcedure
.CommandType = adCmdStoredProc
.CommandText = "spa_Proc"
Set .ActiveConnection = CurrentProject.Connection
.Execute , Array(strParam1, strParam2, intParam3)
End With

Regards

 

[qqqq]

Hi Daran

Thanks for that, how do you pass the value of the parameter for the
parameterised query?

Cheers

Geoff

 

[qqqq]

Hi Eva

Many thanks for your kind assistance

Kind Regards

Geoff

Hello Geof

You can use :

Set spMyProcedure = New ADODB.Command
With spMyProcedure
.CommandType = adCmdStoredProc
.CommandText = "spa_Proc"
Set .ActiveConnection = CurrentProject.Connection
.Parameters.Append .CreateParameter("@Param1",
adVarChar,adParamInput, 10)
.Parameters.Append .CreateParameter("@Param2",
adVarChar,adParamInput, 10)
.Parameters.Append .CreateParameter("@Param3",
adInteger,adParamInput)

.Parameters("@Param1") = strParam1
.Parameters("@Param2") = strParam2
.Parameters("@Param3") = intParam3

.Execute
End With

Or

Set spMyProcedure = New ADODB.Command
With spMyProcedure
.CommandType = adCmdStoredProc
.CommandText = "spa_Proc"
Set .ActiveConnection = CurrentProject.Connection
.Execute , Array(strParam1, strParam2, intParam3)
End With

Regards
--
Eva Etxebeste [MS MVP Access]

Hi

Please can someone help?

I am trying to call a paramaterised stored procedure from VBA in a ADP.

All the info I can find on the net use ADO to make a connection, but surely
in a ADP u r already connected

Please can anyone assist.

Cheers

Geoff