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

Question

首先，我真的很抱歉，如果有人及时地指出我回答这个问题的帖子。我在筛选董事会方面并不擅长，但一直在寻找大约一周的时间。许多线程与我的问题类似，但没有一个完全反映了我想要做的或我遇到的问题。我发现的最接近的是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 

这两个访问& Excel是2007年，我在Windows 7,32位操作系统。我正在使用以下VBA引用：MS ADO Ext。 6.0为DDL和安全，MS ActiveX数据Ojects Recordset 6.0 Lib，MS ActiveX对象6.1 Lib，MS Access 12.0对象Lib，OLE自动化。 （抱歉，我还没有发布图像）

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

的debug.print STRSQL命令产量“'UPDATE SET TestTable的BATCH = 'B' WHERE ID = 11"

我还试图“UPDATE SET TestTable的[BATCH] = 'B' WHERE [ID] = 11“等排列，没有成功。

错误是：“运行时错误'-2147217904（80040e10）'：没有给出一个或多个必需参数的值。”

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

，Mike Shanahan

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非常helful调试查询。