sql - 从 ADODB 命令/记录集更新 ACCESS 时出错

Question

首先，如果有人及时将我指向一个回答了这个问题的帖子，我真的很抱歉。我不擅长筛选板子，但已经搜索了大约一个星期。许多线程与我的问题相似，但没有一个完全反射(reflect)我正在尝试做的事情或我遇到的问题。我找到的最接近的已发布 here .到达那里的解决方案并没有解决我的问题。

我正在尝试使用 VBA 从更新 Excel 工作表中更新 ACCESS 2007 数据库中的记录。我已经完成了将信息从 ACCESS 获取到 Excel，并从 Excel 获取到我的记录集。现在，我需要使用填充的记录集更新 ACCESS。

Public Sub Read_Spreadsheet()
    Dim strSql As String, target_fields As String
    Dim fuel_table As String, new_values As String
    Dim roww As Integer, coll As Integer
    Dim i As Integer, n As Integer, mbrs(32) As Integer
    Call Load_Globals

'   Configure ADODB connection, command, recordset objects
    With cn1
        .Provider = "Microsoft.JET.OLEDB.4.0"
        .ConnectionString = "Data Source = " & Src_WB_nm & "; " & _
            "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
        .Open
    End With
    Set cmd1.ActiveConnection = cn1
    cmd1.CommandType = adCmdText
    cmd1.CommandText = "SELECT * FROM [" & Src_WS_nm & "$]"
    With rs1
        .CursorLocation = adUseClient   ' used 3 previously
        .CursorType = adOpenDynamic     ' used 1 previously
        .LockType = adLockOptimistic
        .Open cmd1
    End With
    Debug.Print "Excel Connection established; recordset created."

    Debug.Print "Fields: " & rs1.Fields.count
    Debug.Print rs1.Fields(0).name
    Debug.Print rs1.Fields(1).name

'--------------------------------------------------------------------------
    With cn2
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & Dest_DB
        .Open
    End With
    With rs2
        .CursorLocation = adUseClient   ' used 3 previously
        .CursorType = adOpenDynamic     ' used 1 previously
        .LockType = adLockOptimistic
    End With
    Debug.Print "Access connection established."

'--------------------------------------------------------------------------
'  NOTE to S.O. readers,  Two nested loops are commented out below
'  These will eventually loop through an uncertain number of fields
'  (~10) and records (~2000) to make all the SQL updates.  For debugging,
'  I'm just trying to get 1 pass to be successful.
'
'    For n = 1 To rs1.RecordCount
'  strSql = "SELECT ID, FSERIAL FROM TESTTABLE WHERE ID = 1"
        strSql = ""
        i = 1
 '       For i = 1 To rs1.Fields.count - 1
            If i <> 1 Then strSql = strSql & ", "
            strSql = strSql & " SET [" & rs1.Fields(i).name & "] = " & Chr(39) & rs1.Fields(i).Value & Chr(39)
 '       Next i
        strSql = "UPDATE " & Dest_Table & strSql & " WHERE [ID] = " & rs1.Fields(0).Value
        strSql = "UPDATE TESTTABLE SET BATCH = 'B' WHERE ID = 11"

        Debug.Print strSql
        Set cmd2 = New ADODB.Command
        With cmd2
            .ActiveConnection = cn2
            .CommandType = adCmdText
            .CommandText = strSql
            .Execute , , adCmdText + adExecuteNoRecords
        End With
   '     CP.Cells(27 + n, 4) = rs1(0)
   '     CP.Cells(27 + n, 5) = rs1(1)
        rs1.MoveNext
        Set cmd2 = Nothing

'    Next n

 '   cmd2.CommandText = "SELECT ID, FSERIAL FROM TESTTABLE WHERE ID = 1"
 '   cmd2.CommandText = "UPDATE TESTTABLE SET BATCH = B WHERE ID = 1"
 '   Debug.Print cmd2.CommandText
 '   rs2.Open cmd2
 '   CP.Cells(28, 4).CopyFromRecordset rs2

    Call Close_Connections
End Sub

Access 和 Excel 都是 2007，我在 Windows 7 32 位操作系统中。我正在使用以下 VBA 引用:MS ADO Ext.适用于 DDL 和安全性的 6.0、MS ActiveX Data Ojects Recordset 6.0 Lib、MS ActiveX Objects 6.1 Lib、MS Access 12.0 Object Lib、OLE 自动化。 (对不起，我还不能发布图片)

一切正常，直到 cmd2.execute 命令(这是调试器突出显示的行)。如果我用一个简单的静态 SELECT 替换 SQL 查询并将其转储到 rs2 中，它工作正常。只有当我尝试更新时才会遇到问题。

debug.print strSQL 命令产生“'UPDATE TESTTABLE SET BATCH = 'B' WHERE ID = 11”

我还尝试了“UPDATE TESTTABLE SET [BATCH] = 'B' WHERE [ID] = 11”和其他排列，但没有成功。

错误是:“运行时错误'-2147217904(80040e10)':没有为一个或多个必需参数提供值。”

谢谢您的帮助!我非常感谢，并且一定会对解决方案进行排名/标记。

,迈克·沙纳汉

Answer 1

您的查询格式不正确。我想你想要的是:

For i = 1 To rs1.Fields.count - 1
    if i<>1 Then strsql = strsql & ", "
    strSql = strSql & "[" & rs1.Fields(i).name & "] = " & rs1.Fields(i).Value
Next i

strsql = "UPDATE " & Dest_Table & " SET " & strSql & " WHERE [ID] = " & rs1.Fields(0).Value

尽管如此，这还是假设所有值都是数字的。您仍然需要解决这个问题，以便与字符串对应的值用单引号括起来。例如，您的测试查询应该是:

.CommandText = "UPDATE TESTTABLE SET BATCH = 'B' WHERE ID = 1"
'                                            ^^^

正如评论中所建议的，一个简单的 Debug.Print strsql对调试您的查询非常有帮助。