能否通过Excel用户表单多条件WHERE语句更新MS Access考勤数据库？

开发考勤监控系统，需支持讲师通过表单修改学生考勤状态，表单包含courseCode、subject、classDate、studentID、name、attendance status、excuse字段。使用VBA连接Access数据库更新数据时，出现“expected end of statement”错误，代码如下：

Private Sub CommandButton1_Click()

Dim cnt As ADODB.Connection
Dim db_path As String
Dim db_str As String

db_path = "filepath;"
Set cnt = New ADODB.Connection

db_str = "provider=Microsoft.ACE.OLEDB.12.0; data source=" & db_path
cnt.Open (db_str)

insert_str = "update Classdate set attendanceStatus = '" & cmbUpdateStatus.Value & "' and Excuse = '" & txtUpdateExcuse.Value & "' where classDate = '" & cmbUpdateDate.Value "' and courseCode = '" & cmbUpdateCourseCode.Value & "' and studentID = '" & cmbUpdateStudentID.Value & "'"

cnt.Execute (insert_str)

MsgBox "Updated sucessfully", vbInformation

Set cnt = Nothing

错误修复与优化 #

• SQL语句格式错误
  UPDATE语句的SET子句中，多个字段更新需用逗号分隔，而非AND。原代码中attendanceStatus = 'xxx' and Excuse = 'xxx'应改为attendanceStatus = 'xxx', Excuse = 'xxx'。

• 字符串拼接缺失符号
  cmbUpdateDate.Value后缺少&符号，导致字符串拼接中断，需修正为cmbUpdateDate.Value & "'"。

• 变量未声明
  insert_str未声明，建议添加Dim insert_str As String，避免隐式变量引发的潜在问题。

• 数据库路径多余分号
  db_path = "filepath;"中的分号需删除，改为db_path = "filepath"，否则会导致连接字符串格式错误。

• 优化：使用参数化查询避免风险
  直接拼接字符串易引发SQL注入，也无法处理含单引号的特殊内容，推荐使用参数化查询，示例代码如下：

Private Sub CommandButton1_Click()
    Dim cnt As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim db_path As String
    Dim db_str As String

    db_path = "你的数据库文件实际路径"
    Set cnt = New ADODB.Connection
    db_str = "provider=Microsoft.ACE.OLEDB.12.0; data source=" & db_path
    cnt.Open db_str

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnt
    cmd.CommandText = "UPDATE Classdate SET attendanceStatus = ?, Excuse = ? WHERE classDate = ? AND courseCode = ? AND studentID = ?"
    
    ' 绑定参数
    cmd.Parameters.Append cmd.CreateParameter("status", adVarChar, adParamInput, 50, cmbUpdateStatus.Value)
    cmd.Parameters.Append cmd.CreateParameter("excuse", adVarChar, adParamInput, 255, txtUpdateExcuse.Value)
    cmd.Parameters.Append cmd.CreateParameter("classDate", adDate, adParamInput, , cmbUpdateDate.Value)
    cmd.Parameters.Append cmd.CreateParameter("courseCode", adVarChar, adParamInput, 20, cmbUpdateCourseCode.Value)
    cmd.Parameters.Append cmd.CreateParameter("studentID", adVarChar, adParamInput, 20, cmbUpdateStudentID.Value)
    
    cmd.Execute
    MsgBox "更新成功", vbInformation

    ' 释放资源
    Set cmd = Nothing
    Set cnt = Nothing
End Sub

内容的提问来源于stack exchange，提问作者StudentOfLife