Results 1 to 13 of 13
  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
    Sep 2011
    Posts
    12

    Problem is not with code

    I made a much simpler stored procedure (SP) and the code works fine (needed to get rid of rs.Open though since it said it was already open). The problem appears to be with the complexity of the SP. I have the same issue when trying to do the same in ASP. Everything works fine with simple SP (with same type and number of inputs) but not with the one I want to use (much more complex). I am able to get it to work in ASP.NET, but I much prefer to get this to work directly from Access. Why would the complexity of the SP make any difference. The last line of both the complex and the simple SP is "SELECT * FROM tablex". The difference is that the simple SP uses an existing table in the database and the complex one uses a table [#BigOne] that is created by the SP based upon data in several tables in complex JOIN relationships.

    I can post the code if anyone is interested in helping and if this would help.

    Thanks!

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    How long does your complex query take to run?

    It could be that the query is timing out in your VBA/ASP code and you need to increase the timeout period. Default is 20 seconds I think.

  4. #4
    Join Date
    Sep 2011
    Posts
    12
    It does take a while to run and I was thinking the same thing when running it in Query window on SQL server.

    I added the line CurrentDb.QueryTimeout = 700
    but still same problem.

    I didn't think this would work though since it gives me compile error: Syntax error or access violation

    It "bombs out" immediately so not a timeout issue.
    Last edited by hollis; 09-05-11 at 19:22. Reason: new info

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    You should be able to add a Connect Timeout or Connection Timeout keyword in you connection string I think...

    Code:
    Cmd1.ActiveConnection = "Provider=SQLOLEDB;Data Source=xxx;Initial Catalog=xxx;Integrated Security=SSPI;Connection Timeout=120"
    Time is in seconds....

  6. #6
    Join Date
    Sep 2011
    Posts
    12
    I'll try it this way, but as stated, it doesn't appear to be a timeout issue. I'm trying different versions of SP to simplify and troubleshoot to determine exactly what part of the SP is causing this to bomb out.

  7. #7
    Join Date
    Sep 2011
    Posts
    12
    Since the SP creates tables and the error message mentions access issue, my bet is that I need to make sure I have needed privileges to create tables. I bet this is the problem. Let me see if I can figure it out and test it.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    You are only creating a temp table right? Shouldn't be many permissions needed for that.

    Did you want to post your stored proc code incase there is something obvious there?

  9. #9
    Join Date
    Sep 2011
    Posts
    12
    Thanks for the offer, but I'm making great progress. I cut the SP up into pieces and created a new one and started pasting pieces of the original into the new test SP and kept adding more and more and testing until it wouldn't work.

    Somehow I finished pasting what I believe to be the entire version of the original SP into the test one and IT WORKS! But when I change the name of the SP in the code to point to the original one, it break!

    Now I'm going to compare the two versions and see what the heck is the difference or if somehow the name of the original SP has something to do with it. Pretty strange but I'm making great progress.

    I do REALLY appreciate your help!

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    How different was the name of the proc? It's not something to do with the hyphen in the name is it? Or the schema that it is in perhaps?

  11. #11
    Join Date
    Sep 2011
    Posts
    12

    Fixed!

    I can't believe it. I spent HOURS AND HOURS trying to debug, research and fix this thing and it came down to THE DARN NAME of the SP!!!!

    I changed the name to EZ-2000 to EZ and now it works! Incredible.

    But there still is one issue (maybe should start new thread). I try to use GetRows() to convert result into array and then close connections. If I use GetRows(n) where n is a number less than the max rows the SP returns, everything works. If I just use GetRows() or n is more than number of rows SP can return, it bombs.

    From my research, it has something to do with SP not being "static" but don't know how to change this with code that I'm using.

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Can you point me to the research around static?

    I think you would need to change your cursor when opening the recordset. From memory by default you get a forward only cursor that doesn't support a lot of properties like row count etc. This may be causing your issue with getRows().

  13. #13
    Join Date
    Sep 2011
    Posts
    12

    Lightbulb

    I started off trying to get number of records of ADO rs when executing my stored procedure using rs.RecordCount but it always returned -1 although the rs had thousands of records. Doing a search, I read that using static cursor type should fix the problem and I also read that changing cursor to client side should fix that but also came across lot of advice that it is better to use GetRows to put all records from recordset into an array then close connections to backend server and get records from the array. You can also use UBound(rsGetRows, 2) + 1 (where rsGetRows=rs.GetRows) to get the count of records returned from the stored procedure.

    Going to Recordset Object Basics gave lots of detailed documentation on how to set type of cursor.

    I used the following code for testing:

    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    Set rs = Cmd1.Execute()
    MsgBox rs.RecordCount
    rsGetRows = rs.GetRows
    MsgBox UBound(rsGetRows, 2) + 1
    MsgBox rsGetRows(1, 1)

    rs.RecordCount still returns -1, but GetRows works and UBound does result in number of records returned.

    Thanks again for the help.

Posting Permissions

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