Run Parameterized SQL Server Stored Proc From VBA

[jo15765]

I am attempting to run a sql server stored procedure from access vba, but I keep getting an error of

Conversion failed when converting date and/or time from character string.

This is my syntax - what do I need to alter in order for this to successfully execute?

Private Sub btnConnect()
Dim cmd As ADODB.Command, d1 As String, d2 As String
	Set cmd = New ADODB.Command
	d1 = "'" & Me.d1 & "'"
	d2 = "'" & Me.d2 & "'"
	cmd.ActiveConnection = "Provider=sqloledb;Server=Server;Database=DB;Trusted_Connection=yes;"
	cmd.CommandType = adCmdStoredProc
	cmd.CommandText = "SQLStoredProc"
	cmd.Parameters.Append cmd.CreateParameter("@d1", adVarChar, adParamInput, 255, d1)
	cmd.Parameters.Append cmd.CreateParameter("@d2", adVarChar, adParamInput, 255, d2)
	cmd.Execute
End Sub




ALTER Procedure [dbo].[SQLStoredProc]
(
	   @d1 varchar(100)
	   ,@d2 varchar(100) 
)
As


Select name, address, phone from employees where hiredate between @d1 And @d2

[pbaldy]

If hiredate is a date/time field, I'd declare the variables as such, and just pass dates without the apostrophes.

[jo15765]

If hiredate is a date/time field, I'd declare the variables as such, and just pass dates without the apostrophes.

I am unfortunately not able to modify the stored procedure. So I am stuck dealing with it as a varchar value. However, it will be passing in a date 03/15/2017 format

[jo15765]

If I format my dates like so

d1 = Format(Me.d1, "YYYY-MM-DD")

I now get a query timeout error. How do I extend the amount of time the stored procedure can run before it times out?

[pbaldy]

I'd have to test using varchar, which I'll try to do if it slows down. Have you tried a pass through query? I only use a command object to execute action queries. A simple select I'd use a pass-through.

[jo15765]

I am actually running a huge CTE query that runs an insert into a table and I select from that table, so I do not think a pass-thro would be beneficial here (think being the key word). I only posted enough for example sake to show what I was trying to achieve.