访问 VBA：SQL 查询导致 UPDATE 语法错误

Access VBA: SQL query causes UPDATE syntax error

提问by lhparker

I have a database with linked tables- Staff, Courses and Training_Record. Each staff member has a numeric primary key, as does each course and each entry in the Training_Record table. The Staff_ID and Course_ID in the Training_Record reference records in Staff and Courses.

我有一个带有链接表的数据库 - Staff、Courses 和 Training_Record。每个员工都有一个数字主键，每个课程和 Training_Record 表中的每个条目也是如此。Staff and Courses 中 Training_Record 参考记录中的 Staff_ID 和 Course_ID。

When a staff member or course is added, the Training_Record (fields: Staff_ID, Course_ID, Date_Taken, Notes) has staff,course records inserted- so adding staff member 1 would insert records (1,1,,,), (1,2,,,) etc, adding course 8 would insert records (1,8,,,), (2,8,,,) and so on. This works.

添加员工或课程时，Training_Record（字段：Staff_ID、Course_ID、Date_Taken、Notes）已插入员工、课程记录 - 因此添加员工 1 将插入记录 (1,1,,,), (1,2 ,,,) 等，添加课程 8 将插入记录 (1,8,,,), (2,8,,,) 等。这有效。

I then have a form to record training. The user selects the course, enters the date and selects staff members from a listbox. I have a save button which triggers VBA code. The date and course are pulled from the boxes and I loop round the listbox, concatenating selected staff members into a string. This all works and a message box displays, verifying that. Then, an update SQL query should be run, updating the Training_Record.

然后我有一个表格来记录培训。用户选择课程、输入日期并从列表框中选择教职员工。我有一个触发 VBA 代码的保存按钮。日期和课程从框中拉出，我在列表框周围循环，将选定的工作人员连接成一个字符串。这一切正常，并显示一个消息框，验证这一点。然后，应运行更新 SQL 查询，更新 Training_Record。

The problem I have is with the SQL update. I have an update query that will work in the SQL query editor, though it uses written in variables:

我遇到的问题是 SQL 更新。我有一个可以在 SQL 查询编辑器中工作的更新查询，尽管它使用写入变量：

UPDATE Training_Record
SET Date_Taken = '12/12/12'
WHERE Staff_ID IN (1,2,3,4,5) AND Course_ID = 4

This updates the Training_Record to show that staff 1,2,3,4 and 5 took course 4 on 12/12/12. However, in VBA this will not work. This is my SQL query in VBA:

这将更新 Training_Record 以显示员工 1、2、3、4 和 5 在 12/12/12 参加了课程 4。但是，在 VBA 中这是行不通的。这是我在 VBA 中的 SQL 查询：

strSQL = "UPDATE Training_Record" _
       & "SET Date_Taken = (" & strDate & ")" _
       & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")"

DoCmd.RunSQL strSQL

The error that the code generates is "Run-time error '3144': Syntax error in UPDATE statement." and the debugger highlights the DoCmd.RunSQL statement following the query.The entire VBA code:

代码生成的错误是“运行时错误 '3144'：UPDATE 语句中的语法错误。” 并且调试器突出显示查询后的 DoCmd.RunSQL 语句。整个 VBA 代码：

Private Sub SaveTraining_Click()

Dim db As DAO.Database
Dim VarItem As Variant
Dim strCriteria As String
Dim strDate As Variant
Dim strCourse As Variant
Dim strSQL As String

Set db = CurrentDb()

'Extract the course ID and the training date from the form
strCourse = Me!CourseID.Value
strDate = Me!TrainingDate.Value

'Dealing with empty boxes- zero length
If IsNull(strCourse) Then
    MsgBox "Please select a course." _
        , vbOKOnly, "No course selected"
End If

If IsNull(strDate) Then
    MsgBox "Please enter a date." _
        , vbOKOnly, "No date given"
End If

If StaffMembers.ItemsSelected.Count = 0 Then
    MsgBox "Please select staff members." _
        , vbOKOnly, "No staff members"
End If

If (Not IsNull(strCourse)) And (Not IsNull(strDate)) And (StaffMembers.ItemsSelected.Count > 0) Then

    'Extract each selected member and concatenate into a string for sql query

    For Each VarItem In Me!StaffMembers.ItemsSelected
        strCriteria = strCriteria & "," & Me!StaffMembers.ItemData(VarItem)
    Next VarItem

    'Gets rid of extra comma on query string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

    'Message box
    MsgBox ("Staff: " & strCriteria & vbNewLine & "Date: " & strDate & vbNewLine & "Course: " & strCourse & vbNewLine & "No. Selected staff: " & StaffMembers.ItemsSelected.Count)

    strSQL = "UPDATE Training_Record" _
           & "SET Date_Taken = (" & strDate & ")" _
           & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")"

    DoCmd.RunSQL strSQL

End If

Set db = Nothing

End Sub

TL;DR I can't make a SQL UPDATE query run in VBA

TL;DR 我无法在 VBA 中运行 SQL UPDATE 查询

I've got a feeling that it's an error in syntax somewhere, but I can't find where. Any ideas/advice would be much appreciated, thanks.

我有一种感觉，它是某个地方的语法错误，但我找不到在哪里。任何想法/建议将不胜感激，谢谢。

采纳答案by lhparker

I think you are simply missing spaces at the end of the lines

我认为您只是在行尾缺少空格

You old query print out

你旧的查询打印出来

UPDATE Training_RecordSET Date_Taken = ()WHERE Staff_ID IN () AND Course_ID = ()

as you can see there will be a name collision before keywords SETand WHERE

如您所见，在关键字SET和WHERE

therefore change your strSQLto

因此改变你strSQL的

strSQL = "UPDATE Training_Record " _
       & "SET Date_Taken = (" & strDate & ") " _
       & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")"

which prints out as (with no values provided)

打印为（没有提供值）

UPDATE Training_Record SET Date_Taken = () WHERE Staff_ID IN () AND Course_ID = () 

which in terms of SQL syntax is correct

在 SQL 语法方面是正确的

If I were you I would also check the data types of columns in your Training_Recordtable

如果我是你，我也会检查Training_Record表中列的数据类型

Usually(and this applies to Type-mismatch error),

通常（这适用于类型不匹配错误），

for dates you wrap the variable or value on both sides with #

对于日期，您将变量或值包装在两侧 #

example & "SET Date_Taken = (#" & strDate & "#) ...

例子 & "SET Date_Taken = (#" & strDate & "#) ...

for strings you use single quotes '

对于字符串，您使用单引号 '

example WHERE Operator_Name = ('" & operName & "') ...

例子 WHERE Operator_Name = ('" & operName & "') ...

for numerical values you do not need to use anything but casting to provide the correct data type

对于数值，除了强制转换之外，您不需要使用任何其他方法来提供正确的数据类型

回答by Patrick Honorez

My guess:

我猜：

strSQL = "UPDATE Training_Record" _
       & "SET Date_Taken = (#" & Format(strDate, "mm\/dd\/yyyy") & "#)" _
       & "WHERE Staff_ID IN (" & strCriteria & ") AND Course_ID = (" & strCourse & ")"

If staff_ID is a string:

如果 staff_ID 是一个字符串：

strSQL = "UPDATE Training_Record" _
       & "SET Date_Taken = (#" & Format(strDate, "mm\/dd\/yyyy") & "#)" _
       & "WHERE Staff_ID IN ('" & strCriteria & "') AND Course_ID = (" & strCourse & ")"