Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    12

    Question 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.

    Any help/ideas?


    -- 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

    Cmd1.ActiveConnection = "Provider=SQLOLEDB;Data Source=xxx;Initial Catalog=xxx;Integrated Security=SSPI;"
    Cmd1.CommandText = "[dbo].[EZ-2000]"
    Cmd1.CommandType = adCmdStoredProc

    -- 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"

    Cmd1.Parameters.Refresh
    Cmd1.Parameters("@BegDate").Value = StartDate
    Cmd1.Parameters("@EndDate").Value = EndDate

    Set rs = Cmd1.Execute()
    rs.Open Cmd1

    --- above code runs for a while and any later code that tries to extract data from rs results in error message that rs is not open ---

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  3. #3
    Join Date
    Sep 2011
    Posts
    12
    Thanks, rok, for the input, but I recently posted that I know it is not a connection issue since I get it to work on simple stored procedure.

    You are right about the parameter comment and the append is apparently more efficient but more variables involved so I'm using this method first to get it to work and then I will optimize.

    Thanks!

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Dates can sometimes be problematic when you are passing them through.

    Have you checked to see exactly what is being run on the server? Can you run a profile and see what the parameters are being set to?

    When you say the code runs for a while do you know what line it actually "stalls" on? Is it the refresh or the open??

  5. #5
    Join Date
    Sep 2011
    Posts
    12
    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?
    Last edited by hollis; 09-05-11 at 19:05.

  6. #6
    Join Date
    Sep 2011
    Posts
    12

    Problem is fixed

    I was able to fix this. The problem was the name of the SP. It used a hyphen. I removed the hyphen (only after hours and hours of work and pulling out my hair) and all works fine now.

    I had similar thread in VB section but wanted to close this one up in case someone has same problem I had and comes across this thread for help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •