Running a Stored Procedure Using a Form Event

[Altemir]

I have an Access Data Project (ADP) in which:

1) I want to run a stored procedure (that returns no records)
triggered by the AfterUpdate event of a form control

2) The value contained in the form control (after being updated) needs
to be passed to the stored procedure as a parameter.

What code do I need to put in the AfterUpdate form control event to
trigger the stored procedure? Whatelse do I need to do in order to
ensure that the form control value gets passed to the stored procedure
as a parameter?

Me.RecordSource "Exec strMyStoredProcedure" doesn't work because the
stored procedure is not intended to return any records that would act
as a record source.

Any ideas?

 

[Norman Yuan]

You can use an ADODB.Command object in the AfterUpdate event handler to
execute that SP in SQL Server. Something like:

Private Sub Form_AfterUpdate()

Dim cmd AS ADODB.Command
Set cmd=New ADODB.Command
Set cmd.ActiveConnection=CurrentProject.Connection
cmd.CommandType=adCmdStoredProc
cmd.CommandText="MyStoredProcedure"

'Add necessary parameters here

cmd.Execute

End Sub

 

[giorgio rancati]

Hi Altemir,
try this

Method #1
----
Private Sub Form_AfterUpdate()

CurrentProject.Connection.[SpName] Me!ContolName

End Sub
----

Method #2
----
Private Sub Form_AfterUpdate()

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SpName"
cmd.CommandTimeout = 300
cmd.Parameters.Append _
cmd.CreateParameter("@Param1", adInteger, adParamInput, ,
Me!ControlName)
cmd.Execute

Set cmd = Nothing

End Sub

 

[Vadim Rapp]

A> What code do I need to put in the AfterUpdate form control event to
A> trigger the stored procedure?

currentproject.connection.execute "exec mystoredprocedure '" & control.value
& "'"

Vadim Rapp

 

[Altemir]

Thank you Vadim for your excellent suggestion!! Very simple and to the
point! Works great.

 

[aaron.kempf]

uh why not docmd.runsql?

i mean.. i think that going through the Access error handler is
better-- more robust-- because cnn.execute doesn't always handle errors
like it should..

RIGHT?