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

Question

在以下代码中，我尝试使用 ADODB 从 Command 对象打开记录集，但它告诉我该记录集不可更新。我很难做到这一点。

当我尝试将 .Open 方法与 Command.Execute 一起使用时，传递 adOpen{Static|Dynamic}、adLock{Optimistic|Pessimistic}，它给了我以下错误:

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

如何使用 ADO 从命令对象打开可更新的记录集？

Answer 1

为这个答案的迟到道歉，这是为了任何遇到这个与我自己的问题完全匹配的问题的人的利益。

您可以使用 ado 命令作为记录集的源参数，而不是使用 cmd.Execute。此时您可以使用 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