Running Stored Procedures 1

[LeonAtRC]

I want to run a stored procedure from a module in a .adp Access program.
Code would be similiar to:

DoCmd.OpenStoredProcedure("MyProc")

Even through I know the store procedure named "MyProc" exists I still get an error message saying Access can't find the object.
I then iterated through the obj.AllStoredProcedures and tried the "DoCmd" again on the name Access found but I still get the same error message. (i.e. Access finds it and then tells me it doesn't exist!)
I've been to MSDN, I've searched this site's FAQ's, and I've also tried every different syntax I can think of but still no success.
(I've also tried:

DoCmd.OpenView "MyProc"

which is what the documentation recommends but I still get the same error message.)
I'm running MS Access 2000 and MS SQL 2000 Server.
Any suggestions would be greatly appreciated.
Thanks!
-Leon

 

[rorubin]

You should use ADO.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00f10.asp

 

[cmmrfrds]

A stored procedure can be the row source for a Form or Report in an Access Project assuming it returns a resultset. A flexible way to handle stored procedures is through the ADO Command Object. Here is an example with a Stored Procedure that has several parameters.

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, param3 As Parameter, param4 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 param3 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param3
param3.Value = 3
Set param4 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param4
param4.Value = 3

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

' Execute command to run stored procedure
cmd.Execute
'OR
' Return the resultset of a stored procedure to a recordset
Set rst = cmd.Execute