Date: 03/14/06 (SQL Server)    Keywords: programming, sql, security

Hi everyone.

...about programming MS ADO with MS SQL Server.
Is it possible to get a (ADODB) Recordset having NOT Forward-only cursor type from 'Execute' method of ADO Command object?

To be clear, in terms of VBA Code:

Dim adocmd as ADODB.Command
Dim rst as ADODB.Recordset
Set adocmd = New ADODB.Command
' ....... Connecting 'adocmd' via MS SQLOLEDB Provider
Set rst = adocmd.Execute

The problem is that I could not get non-ForwardOnly recordset in the last statement in all cases, and I wish to use the result (rst) as the data source (Recordset property) for a MS Access form. Such forms don't allow to set forward-only recordsets as form data sources, of course.

The parameters of 'Execute' method of ADODB.Command object do not include cursor type setting of the recordset returned.

Thanks in advance for any help.

UPDATED. A solution is found.

Setting the client-side cursor to the active connection may fix the problem. As the documentation says, "Cursors returned by the Execute method inherit this setting", so after such setting the Recordset object returned by Execute method of the Command object has Client-side static cursor - this is appropriate for setting it as a Data Source Recordset for an MS Access Form to browse records.

Here is a VBA sample code. (Of course, in this example using ADODB.Command for executing a simple SELECT Query to return records is senseless, but it's just for example. In my situation I call a stored procedure with parameters (including OUTPUT params and RETURN_VALUE) that also return records, and I wanted to use all advantages of ADODB.Command object for such multiple calls).

    Dim cnn As ADODB.Connection
    Dim adocmd As ADODB.Command
    Dim rst As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    cnn.CursorLocation = adUseClient
    cnn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=TestDB;Persist Security Info=False;Integrated Security=SSPI;"
    
    Set adocmd = New ADODB.Record
    With adocmd
        Set .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "SELECT 10 AS foo UNION SELECT 20"
        Set rst = .Execute
    End With

' Now `rst` has CursorLocation=adUseClient and CursorType=adOpenStatic
' and may be used as a Recordset for an MS Access Form
' If we are in form module, then one may set
'       Set Me.Recordset = rst
' or even we could write before
'        Set Me.Recordset = .Execute

Source: http://community.livejournal.com/sqlserver/45984.html