Stored Procedures

  • Thread starter Thread starter qqqq
  • Start date Start date
Q

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
 
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
 
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
 
Hi Daran

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

Cheers

Geoff
 
Hi Eva

Many thanks for your kind assistance

Kind Regards

Geoff

Eva Etxebeste said:
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]

qqqq said:
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
Click to expand...
Click to expand...
 
Back
Top