none
VBA excel 2010 - Recordset cursor type RRS feed

  • Question

  • Hi,

    I'm using excel 2010 - 32 bit version

    This is my first of encounters with VBA ( supporting an existing application) :)

    My VBA code thats creating problem

    ADOrst.Open SourceName, GetCurrentConnection(), adOpenDynamic, adLockBatchOptimistic





    Source = query that needs to be executed for e.e. "Select Column1 from Table1"
    adOpenDynamic = 2
    adLockBatchOptimistic = 4

    Above VBA code produces this on SQL server profiler, which when executed throws error.

    declare @p1 int
    set @p1=0
    declare @p3 int
    set @p3=229378
    declare @p4 int
    set @p4=294916
    declare @p5 int
    set @p5=0
    
    exec sp_cursoropen @p1 output,N'SELECT  TOP 100 Column1 from Table1',@p3 output,@p4 output,@p5 output
    select @p1, @p3, @p4, @p5




    Error details on SQl server

    Msg 16955, Level 16, State 2, Line 1
    Could not create an acceptable cursor.
    Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1 [Batch Start Line 0]
    The cursor was not declared.


    How do I resolve this...? Or what should I change the cursor type to so that I can get the ball rolling.

    Thanks for the help!

    • Edited by StSingh Tuesday, February 19, 2019 2:46 PM
    Tuesday, February 19, 2019 2:38 PM

All replies

  • This is what I use to connect to SQL Server.

      Set ssDb = New ADODB.Connection
      Set rst1 = New ADODB.Recordset
      rst1.CursorLocation = adUseClient
      Set rst2 = New ADODB.Recordset
      rst2.CursorLocation = adUseClient
      
      svrName = ""
      dbName = ""
      userId = ""
      password = ""
    
      cs = ""
      cs = cs & "Driver={SQL Server};Server=" & svrName & ";Database=" & dbName
      cs = cs & ";Uid=" & userId & ";Pwd=" & password & ";"
      ssDb.Open cs
    

    Tuesday, February 19, 2019 3:28 PM
  • The user does not have the appropriate rights to the SQL database.
    • Proposed as answer by Victor Gruber Tuesday, February 19, 2019 3:43 PM
    • Unproposed as answer by Victor Gruber Wednesday, February 20, 2019 9:38 AM
    Tuesday, February 19, 2019 3:43 PM
  • Thanks Victor & mogulman52

    I have tried changing userid but same issue

    Tuesday, February 19, 2019 5:35 PM
  • Can you connect using SSMS?  If you can you should be able to connect using VBA.
    Tuesday, February 19, 2019 8:36 PM
  • Yes I'm able to connect using SSMS. I don't think connectivity is the issue ( my guess).

    SQL server throws error when the statement is executed...

    Tuesday, February 19, 2019 8:53 PM