Trying to use VBA variable in Access UPDATE Statement

Question

So as the title says I am trying to use a VBA variable within an update statement. This is being used because I want to loop through each cell in a range and subtract a value from the worksheet from a value within the table corresponding to that iterations cell reference. However, statement runs an error saying "too many parentheses." However, this is odd to me b/c I pulled the SQL syntax directly out of access. I guess I am missing something? I will post both versions of the code:

Code with vba variables :

Private Sub UPDIZZLE()
    Dim rCell As Range
    Dim rRng As Range
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConnection As String
    Dim QUV As Long
    Dim IID As String

    Set rRng = ActiveWorkbook.Sheets("Sheet1").Range("b2:b100")

    For Each rCell In rRng.Cells
        If rCell <> "" And rCell.Value <> 0 Then
           IID = rCell
           QUV = rCell.Offset(0,x).Value
            strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ashleysaurus\Desktop" & "\" & "xyzmanu3.accdb"
            con.Open strConnection
            rs.Open "UPDATE Stuff SET Stuff.Quantity = Quantity-" & QUV & " WHERE (((Stuff.[ItemID])=" & IID & "));", con
            con.Close
        End If
    Next rCell

End Sub

code without variable:

    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ashleysaurus\Desktop" & "\" & "xyzmanu3.accdb"
    con.Open strConnection
    rs.Open "UPDATE Stuff SET Stuff.Quantity = Quantity-1 WHERE (((Stuff.[ItemID])=Thingamagig));", con
    con.Close

Is it possible that the `IID` value itself contains a parenthese? — RGA, Jul 12 '16 at 12:52
As `IID` is a string shouldn't it be `rs.Open "UPDATE Stuff SET Stuff.Quantity = Quantity-" & QUV & " WHERE (((Stuff.[ItemID])='" & IID & "'));", con` (single quotes around `IID`). — Darren Bartrup-Cook, Jul 12 '16 at 12:55
you know I couldve made a pretty dumb noob mistake, ill try it and post back — Doug Coats, Jul 12 '16 at 12:57
Don't use an ADODB.Recordset object to do an UPDATE; use an ADODB.Command object instead. Then you can use a *parameterized query* to specify the values to be included in the update. — Gord Thompson, Jul 12 '16 at 16:56
@DarrenBartrup-Cook, if youd want feedback for a correct answer please pst as answer :) — Doug Coats, Jul 12 '16 at 22:34

score 1 · Accepted Answer · answered Jul 13 '16 at 07:55

1

As IID is a string shouldn't it be

    rs.Open "UPDATE Stuff SET Stuff.Quantity = Quantity-" & QUV & " WHERE (((Stuff.[ItemID])='" & IID & "'));", con

(single quotes around IID)

answered Jul 13 '16 at 07:55

Darren Bartrup-Cook

18,362
1
23
45

Trying to use VBA variable in Access UPDATE Statement

1 Answers1

Linked