Can I run SQL Server stored procedures from Access mdb

[dw!]

I want to migrate from an Access mdb database to Access mdb front end/SQL back end.
I want the flexibility of mdb, with local tables etc, so don't want to go to adp.
I would like to run stored procedures in my SQL server as well, and I know how to do that using pass through queries, but that method is limited in the kind of recordset it returns (e.g. they are read only).
I know how to write code in ADO to run stored procedures in a MS Access adp - but the same code won't run in my mdb front end because the database can't find the stored procedures.
My question is - is there a way to use ADO command syntax to run stored procedures in SQL from within a Microsoft mdb database?

 

[pilsdumps]

I use a small class to run them;

Option Compare Database
Option Explicit

'run Stored Procs and return recordset of output

Private comm As Command
Private conn As New Connection
Private m_sp_name As String

Private Sub Class_Initialize()

Set conn = CurrentProject.Connection
Set comm = New Command

End Sub

'add each parameter to the comm object
Public Sub add_param(param_name As String, _
param_type As DataTypeEnum, _
param_direction As ParameterDirectionEnum, _
param_value As Variant, _
Optional param_size As Long)


Dim param As New Parameter
param.Name = param_name
param.Type = param_type
param.direction = param_direction
param.Value = param_value

If Not IsMissing(param_size) Then
param.Size = param_size
End If

comm.Parameters.Append param

End Sub

'run the procedure returning the results to the passed recordsetPublic Sub Run_SP_Command_Reader(ByRef rs As Recordset)


Set rs = comm.Execute

End Sub

'run the procedure returning the results to the passed recordset
Public Sub Run_SP_Command_No_Return()

comm.Execute

End Sub

Private Sub begin_setup()

If m_sp_name = "" Then Stop

With comm
.CommandText = m_sp_name
.CommandType = adCmdStoredProc
.ActiveConnection = conn

End With

End Sub

Public Property Get sp_name() As String
sp_name = m_sp_name
End Property

Public Property Let sp_name(ByVal vNewValue As String)
m_sp_name = vNewValue
Call begin_setup

End Property


You need to add your parameters using the add_param method and then pass a recordset to have it populated with the results, and then can do what you like with it. Not sure if this answers your question, but its what I do and works for me.