the request for procedure 'y' failed because 'y' is a table valued function object

[helenpostle]

I have an Access frontend to a SQL db and I want to retrieve the results from a stored procedure and put them into a temporary table in access but I keep getting the above error message. I've tried using connection.execute and recordset.open but get the same error with either. I've been fiddling about with this for ages and could really do with some pointers. My code is below

Dim qdf As QueryDef
Dim strComm As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

strComm = "Exec st_orgsWithCurrentData " & GroupID & ", '" & MinExtractDate & "'"
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Driver=SQL Server;Server=ServerName;Database=dbName;Trusted_Connection=yes;"
cnn.Open
Set rs = New ADODB.Recordset

'Set rs = cnn.Execute(strComm)
rs.Open strComm, cnn, adOpenDynamic, adLockPessimistic

Debug.Print rs(0), rs(1), rs(2)
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing

 

[helenpostle]

got this working in case anyone else has the same problem:

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
strComm = "select * from st_orgsWithCurrentData(" & GroupID & ", '" & MinExtractDate & "')"
rs.Open Source:=strComm, ActiveConnection:=gcnn