Error Updating ACCESS from ADODB Command / Recordset

Question

First off, I truly apologize if someone promptly points me to a post in which this question was answered. I'm not great at sifting through the boards, but have been searching for about a week. Many threads are similar to my problem, but none exactly mirror what I'm trying to do or the problem I'm having. The closest I've found was posted here. The solution reached there did not solve my issue.

I am trying to update records in an ACCESS 2007 database from an update Excel worksheet using VBA. I have accomplished getting information from ACCESS into Excel, and from Excel into my recordset. Now, I need to update ACCESS with the populated recordset.

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

Both Access & Excel are 2007, and I'm in Windows 7, 32 Bit OS. I'm using the following VBA references: MS ADO Ext. 6.0 for DDL and Security, MS ActiveX Data Ojects Recordset 6.0 Lib, MS ActiveX Objeects 6.1 Lib, MS Access 12.0 Object Lib, OLE Automation. (sorry, I cannot post images yet)

Everything works fine up until the cmd2.execute command (That is the line highlighted by the debugger). If I replace the SQL query with a simple static SELECT and dump it into rs2, it works fine. It's when I try to update only that I get the problem.

The debug.print strSQL command yields"'UPDATE TESTTABLE SET BATCH = 'B' WHERE ID = 11"

I've also tried "UPDATE TESTTABLE SET [BATCH] = 'B' WHERE [ID] = 11" and other permutations, with no success.

The error is : "Run-time error '-2147217904 (80040e10)': No value given for one or more required parameters."

Thank you for your help! I appreciate it very much, and will be sure to rank/flag the solution.

,Mike Shanahan

Answer 1

Your query is ill-formed. I think what you want is:

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

Still, this supposes all values are numeric. You'll need to still work it out so that values corresponding to strings are enclosed with single quotes. For example, your test query should be:

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

As suggested in comments, a simple Debug.Print strsql is very helful to debug your queries.