时间戳

首页 > 解决方案 > UPDATE 语句中的运行时错误 3144 语法错误

vba - UPDATE 语句中的运行时错误 3144 语法错误

问题描述

在访问 VBA 中更新 SQL 语句时出现错误。这是代码：

Dim id As Integer, docNo As String, pNo As String
id = Me.txtID.Value
pNo = Me.txtPENo.Value
docNo = Me.txtdocNo.Value
db.Execute ("UPDATE tblPE SET (PENo='" & pNo & "',DocNo='" & docNo & "') WHERE ID=" & id & ";")

标签： vbams-access

解决方案

为了可读性和可维护性，请考虑通过QueryDefs进行参数化，并避免在 SQL 中连接和标点 VBA 变量：

Dim db As Database, qdef As QueryDef
Dim sql As String

' PREPARED STATEMENT (NO DATA)
sql = "PARAMETERS [prm_pNo] TEXT, [prm_docNo] TEXT, [prm_id] INTEGER;" _
       & "UPDATE tblPE SET PENo=[prm_pNo], DocNo=[prm_docNo] WHERE ID=[prm_id];"

Set db = CurrentDb
Set qdef = db.CreateQueryDef("", sql)

' BIND PARAMS
qdef!prm_id = Me.txtID.Value
qdef!prm_pNo = Me.txtPENo.Value
qdef!prm_docNo = Me.txtdocNo.Value

' EXECUTE ACTION
qdef.Execute

' RELEASE RESOURCES
Set qdef = Nothing: Set db = Nothing

推荐阅读