I have this table function :
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION SP_Aankopen
(
@DateFrom date,
@DateTo date
)
RETURNS TABLE
AS
RETURN
(
select * from dbo.aankopen
where Dbo.aankopen.[DocumentDate] between @DateFrom and @DateTo
)
GO
Now I'm trying to access it from VBA Excel as follows:
Public Function Extract(fromDate As Date, toDate As Date, sp As String) As Recordset
Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = sp
cmd.CommandType = adCmdTable
cmd.Parameters.Refresh
Dim p1 As ADODB.Parameter
Set p1 = cmd.CreateParameter("@DateFrom", adDBTimeStamp)
p1.Value = "2016-01-16" 'sqlformatDate(fromDate)
cmd.Parameters.Append p1
Set p1 = Nothing
Dim p2 As ADODB.Parameter
Set p2 = cmd.CreateParameter("@DateTo", adDBTimeStamp)
p2.Value = "2017-01-15" 'sqlformatDate(toDate)
cmd.Parameters.Append p2
Set p2 = Nothing
Dim rs As New Recordset
Set rs = cmd.Execute()
Set Extract = rs
Set rs = Nothing
End Function
This gives error 8004014 No parameters supplied for the function 'SP_Aankopen' in the following statement :
Set rs = cmd.Execute()
In SSMS this works fine :
USE [Exp_Plucz]
GO
SELECT * FROM [dbo].[SP_Aankopen] (
'2016-01-16','2017-01-15')
GO
What am I doing wrong? Many thanks.
If things have the tendency to go your way, do not worry. It won't last. Jules Renard.