Execute Stored Procedure with parameters from Access Form

[Guest]

I need help executing a stored procedure to update the current record from
Access when a user clicks a command button.

The stored procedure (spSI_Update) is expecting two text parameters (strEID
and strFY) from the Access Form to identify the record to update.

I don't know where to begin. Code samples would be great. thanks.

 

[Guest]

I can tell you what I did, and you can decide
Each SP return a prameter that indicate if the SP accomplished it task or
not, and some SP returns a recordset.

So, I created a PassThrough Query that I assign to it an the appropriate
code to run the SP, and then I open a recordset to check the return values
You need to set the return records property of the query to Yes.

Function RunSP()
Dim db As Dao.DataBase , rs as Dao.RecordSet
Set db = CurrentDb
Application.CurrentDb.QueryDefs("QueryName").SQL= "EXEC SP_Name '" & strEID
& "','" & strFY & "'"
Set rs = db.OpenRecordset("QueryName")
If not rs.Eof then
' Check the return code
End If
End Function

 

[Guest]

You don't say whether you use DAO or ADO. I have done this using ADO as
follows:

Public Function ExecuteSP(ByVal strEID As String, ByVal strFY As Long) As
Integer
On Error GoTo PROC_ERR

Dim conDatabase As New Connection
Dim cmdUpdateRecord As New Command
Dim prmReturnValue As Parameter
Dim prmEID As Parameter
Dim prmFY As Parameter

'Set up the connection
conDatabase.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=MyServer;"
& _
"Initial
Catalog=MyDatabase;" & _
"Persist Security
Info=True;" & _
"User
ID=username;Password=password"
conDatabase.Open

'Configure the command object
Set cmdUpdateRecord.ActiveConnection = conDatabase
cmdUpdateRecord.CommandType = adCmdStoredProc
cmdUpdateRecord.CommandText = "MyStoredProcedure"

'set the parameters for the command
Set prmReturnValue = cmdUpdateRecord.CreateParameter("ReturnVal",
adInteger, adParamReturnValue)
cmdUpdateRecord.Parameters.Append prmReturnValue

'You will probably need to adjust the 'adVarChar' and '255' settings to
your requirements
Set prmEID = cmdUpdateRecord.CreateParameter("EID", adVarChar,
adParamInput, 255, strEID)
cmdUpdateRecord.Parameters.Append prmEID

Set prmFY = cmdUpdateRecord.CreateParameter("EID", adVarChar,
adParamInput, 255, strFY)
cmdUpdateRecord.Parameters.Append prmFY

'Execute the command
cmdUpdateRecord.Execute
ExecuteSP = prmReturnValue.Value

PROC_EXIT:
On Error Resume Next
Set prmReturnValue = Nothing
Set prmEID = Nothing
Set prmFY = Nothing
Set cmdUpdateRecord = Nothing
conDatabase.Close
Set conDatabase = Nothing
Exit Function

PROC_ERR:
ExecuteSP = 999
Resume PROC_EXIT
Resume

End Function

You can use return values in your SP to indicate various errors that may be
trapped by the SP code. Any other unexpected errors would cause the function
to return 999 (or use some other number if this is not appropriate). If the
update completes successfully, the function should return 0. You can also use
output parameters in your SP and get at them using something like

Set prmNewRecordID = cmdAddRecord.CreateParameter("RecordID", adInteger,
adParamOutput)
cmdAddRecord.Parameters.Append prmNewRecordID

so if you use a SP to add a new record to the database you can find out the
new PK value back in Access. N.B. above code is untested butchered code from
one of my applications. It worked before I butchered it, but I won't
guarantee that I haven't broken it by changing it to meet your requirements!