Geek asks and answers

HOWTO: open an ADODB record set from a command object that allows updating?

In the following code, I am trying to open a recordset using ADODB from a Command object, but it tells me that the recordset is not updatable. It's hard for me to do this.

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

Runtime Error "3001" The
arguments are of the wrong type, are out of range, or are in conflict with each other.

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 to open an updatable recordset from a command object using ADO?

+3
source share
1 answer

, , , .

cmd.Execute ado . , adLockOptimistic

.

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

... http://www.vbforums.com/showthread.php?278362-Nonupdatable-Recordset-returned-from-Adodb.command

+5

More articles:


All Articles