尝试在Access UPDATE语句中使用VBA变量 [英] Trying to use VBA variable in Access UPDATE Statement

问题描述

因此，正如标题所述，我正在尝试在更新语句中使用VBA变量.之所以使用它，是因为我想遍历范围内的每个单元格，并从与该迭代单元格引用相对应的表中的值中减去工作表中的值.但是，语句运行错误，提示括号太多".但是，这对我来说很奇怪，因为我直接将SQL语法拉出了访问权限.我想我缺少了什么?我将发布两个版本的代码:

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:

带有vba变量的代码:

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

不带变量的代码:

    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

推荐答案

由于IID是字符串，不应该是

As IID is a string shouldn't it be

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

(有关IID的单引号)

(single quotes around IID)

这篇关于尝试在Access UPDATE语句中使用VBA变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！