VBA Recordset Cursor type

[singhswat]

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=0declare@p3 intset@p3=229378declare@p4 intset@p4=294916declare@p5 intset@p5=0

exec sp_cursoropen @p1 output,N'SELECT  TOP 100 Column1 from Table1',@p3 output,@p4 output,@p5 outputselect@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.


what should I change the cursor type to so that I can get the ball rolling?



Thanks for the help!

 

[Chihiro]

Really depends on what sort of operation you are performing.

If importing data into Excel. I'd use, CursorType:= 3, LockType:=1.

Also I typically set connection object rather than use GetCurrentConnection().

You may find below article useful.
https://docs.microsoft.com/en-us/sq...pen-method-ado-recordset?view=sql-server-2017

But looks like you are trying to execute stored proc through VBA.

There are few different approaches. See link for some examples.
https://stackoverflow.com/questions...ocedure-with-parameters-from-excel-vba-string