HOWTO: Open an ADODB recordset from a command object that allows updating?

Question

In the following code, I try to open a recordset using ADODB from a Command object, but it tells me that the recordset isn't updatable. I'm having a hard time trying to make it so.

When I attempt to use the .Open method with the Command.Execute, passing adOpen{Static|Dynamic}, adLock{Optimistic|Pessimistic}, it gives me the following error:

Runtime error '3001'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Dim cmdActionLog As ADODB.Command
Function LogAction(ActionID As Integer, Optional StartedOn As Date, Optional EndedOn As Date, Optional SuccessFlag As Boolean = True)
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenStatic
    rs.Open cmdActionLog.Execute(Parameters:=Array(ActionID)), , adOpenStatic, adLockOptimistic
    'Set rs = cmdActionLog.Execute(Parameters:=Array(ActionID))
    If Not rs.EOF Then
        If StartedOn Then rs!LAST_STARTED_ON = StartedOn
        If EndedOn Then rs!LAST_ENDED_ON = StartedOn
        rs!SUCCESS_FLAG = SuccessFlag
        rs.Update
    Else
        Debug.Print "No action exists with that ID!  Something is wrong here."
        Stop
    End If
End Function
Sub PrepareLogConnection()
    Dim prmActionID As ADODB.Parameter
    Set cmdActionLog = New ADODB.Command
    With cmdActionLog
        .CommandType = adCmdText
        .ActiveConnection = CurrentProject.Connection
        .Prepared = True 'Optimize for reuse
        .CommandText = "select * from ACTION_LOG where ACTION_ID = ?"
        .Parameters.Append .CreateParameter("ActionID", adBigInt, adParamInput)
    End With
End Sub
Sub test()
    PrepareLogConnection
    Debug.Print "START: " & Now

    For x = 1 To 10
        LogAction 1, Now() 'Test how long it takes with and without Prepared in PrepareLogConnection
    Next x

    Debug.Print "END: " & Now
End Sub

How do I open an updatable recordset from a command object using ADO?

Answer 1

Apologies for the lateness of this answer, this is for the benefit of anyone else who comes across this question which exactly matched my own problem.

Instead of using cmd.Execute you can use the ado command as the source parameter to a recordset.Open at which point you can use the adLockOptimistic flag

E.g.

Public Function GetP(id As Long) As ADODB.Recordset
    If cmdPricingXref Is Nothing Then
        Set cmdP = New ADODB.Command
        With cmdP
            .ActiveConnection = cnM
            .CommandText = "SELECT * FROM A_PR where ID =?"
            Set prmId = .CreateParameter("ID", adNumeric, adParamInput, 6)
            .Parameters.Append prmId
            .CommandType = adCmdText
            .CommandTimeout = 30
        End With
    End If
    cmdP.Parameters("ID").value = id

    'Set GetP = cmdP.Execute()
    Set GetP = New ADODB.Recordset
    'use the ado command as the source parameter instead of the 
    'typical sql statement.  do not include the connection parameter
    GetP.Open cmdP, , adOpenStatic, adLockOptimistic 'change these to your suit needs
End Function

which I eventually found here ... http://www.vbforums.com/showthread.php?278362-Nonupdatable-Recordset-returned-from-Adodb.command