Souris,
here is the answer posted by another user for this same question. I am
feeling lazy so I copied it for you. It should contain what you are needing
or at least set you in the correct direction:
You can use ADO objects to run the stored procedure. Here's an example:
'---- start of code snippet ----
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConnect As String
Dim lngReturnCode As Long
' Open a connection to the back-end database and set up a
' command object to work with it.
Set cnn = New ADODB.Connection
cnn.Open fncGetConnectString()
' The above function, fncGetConnectString(), returns the connect
' string needed to access the SQL Server back-end database,
' extracting it from a linked table's Cronnect property. You
' can hard-code your connect string if you want.
Set cmd = New ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
' Set up and execute a call to the server to execute the
' stored procedure and get the result.
cmd.CommandText = "spInactivatePosition"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("@PositionID") = m_lngOriginalPositionID
cmd.Parameters("@EndDate") = Me!txtEndDate
cmd.Parameters("@User") = CurrentUser()
cmd.Execute , , adExecuteNoRecords
' Get the proc's return code from the Command object.
lngReturnCode = cmd.Parameters("@return_value")
'---- end of code snippet ----
Note that I chose to Refresh the command object's Parameters collection,
rather than creating my own Parameter objects and adding them to the
collection. The method I used does require another round trip to the
server, so you may choose not to do that.
