parameter of update store procedure?

[hu68]

In access ADP I have a store procedure -update the specified record in the table,from a form I collect data about updating,but I don't know how to send these data to the update store procedure,Could some one give me some help?
Any help will be appreciated.

 

[cmmrfrds]

Are you sending the fields to the stored procedure as parameters? How many fields are you updating? There are different techniques for sending the parameters depending on your requirements.

 

[hu68]

Hi,cmmfrds
thanks for your reply.
yes,I will send fields as parameter,there are six Fields to be updated,Could you give me more detail instruction?Thanks!

 

[cmmrfrds]

Here is code that would go in the Access program for example the OnOpen Event. This example uses 2 input parameters of date type. Output parameters can be returned back to the Form in place of a resultset. You can return both parms and resultset but there are some things you will need to know.

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
Set param1 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command to run stored procedure
'' return resultset to forms recordset

Set rst = cmd.Execute

' another option is to return resultset to me.recordsource

This is the way using VBA code, you could also put your paramaters on the Parameter Property of the Data Tab Control on the Form. Notice when you create the parameters in code you have 3 properties that need to be filled in. Whether the parm is input or output, the data type, and and a constant for input or output. The data type of Char or varchar require a 4th property of length. Look up syntax in help.

 

[hu68]

Hi,cmmfrds
Thanks for your clear explanation,I'll try it!