Unanswered: VBA code in Access to get rs from Stored Procedure
I have a stored procedure in MS SQL 2005 and want to create a recordset in Access 2007 VBA from a stored procedure that accepts input for data range for records to be included in the recordset.
The following line works in Management Studio Query window when executed:
EXEC [dbo].[EZ-2000] '1/15/2011','1/30/2011'
I run the code below and it seems to spend some time running the SP but the recordset does not return any records.
-- ealier code to define SP input dates ---
Dim StartDate As Date
Dim EndDate As Date
-- Start and End dates are taken from Access form --
StartDate = CDate(Me.Date_Billed_From_Date)
EndDate = CDate(Me.Date_Billed_To_Date)
-- When I debug, these dates show up as same dates I use in Query above --
--- main code ----
Dim Cmd1 As New ADODB.Command
Dim rs As New ADODB.Recordset
' On Error GoTo Err_CompcareExcel_Click
Set Cmd1 = New ADODB.Command
-- I tried this line without the above 2 lines and next 3 and same results --
'Cmd1.CommandText = "EXEC [dbo].[EZ-2000] '1/15/2011','1/30/2011'"
-- I used the next line to verify that connection to server is fine and it did return a recordset ---
'Cmd1.CommandText = "SELECT * FROM Known_Table"
It's been a while since I used VBA but isn't there a Command.Parameters.Add method that you should be using for setting up your parameters?
Also, can't you create a Connection object and then assign it to the active connection rather than using the connection string approach? From memory there are some differences with how these work and it might be a connection issue not a query execution issue.
Thanks, again. You are right about dates being tricky, but I created a simple SP with dates and have that part working fine. I believe the problem might be that it takes too long and that I need to change the timeout time. Do you know how to increase timeout parameter?