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
– RGA, Commented Jul 12, 2016 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
– Darren Bartrup-Cook, Commented Jul 12, 2016 at 12:55
you know I couldve made a pretty dumb noob mistake, ill try it and post back — Doug Coats
– Doug Coats, Commented Jul 12, 2016 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
– Gord Thompson, Commented Jul 12, 2016 at 16:56
@DarrenBartrup-Cook, if youd want feedback for a correct answer please pst as answer :) — Doug Coats
– Doug Coats, Commented Jul 12, 2016 at 22:34

Darren Bartrup-Cook · Accepted Answer · 2016-07-13 07:55:12Z

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, 2016 at 7:55

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying to use VBA variable in Access UPDATE Statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related