如何通过表单向MS Access表添加多字段数据并解决INSERT INTO语法报错

问题原因 #

• 特殊字符字段未转义：Access 中字段名包含/、&等特殊符号时，必须用方括号[]包裹，否则会被识别为运算符触发语法错误。你代码中需要加方括号的字段包括：Emp_Expertise/Job_Knowledge、Emp_Drive_Change_&_Innovation、Strengths/Achievements、Sup_Expertise/Job_Knowledge、Sup_Drive_Change_&_Innovation。
• SQL拼接缺少空格：你拆分的多段SQL字符串拼接后出现内容粘连，比如Insert into Performance_Reports后面直接跟左括号，会被识别为非法语法。
• 变量未正确解析：你直接把VBA变量写在了SQL字符串内部，Access不会自动读取变量值，只会将其识别为字段名；同时不同类型的变量需要添加对应分隔符：文本类型加单引号、日期类型加#、数字类型直接拼接即可。

修复后的代码示例 #

请根据你实际的变量类型调整对应分隔符：

Private Sub addRecord_Click()
    Dim strSQL As String
    ' 拼接SQL，特殊字段加方括号、段间补空格、变量按类型拼接
    strSQL = "Insert into Performance_Reports " _
    & "(Report_Id, Badge_Id, Review_Date, Progression, Overall_Rating, Next_Review, " _
    & "[Emp_Expertise/Job_Knowledge], Emp_Quality, Emp_Dependability, Emp_Delivering_Solutions, " _
    & "Emp_EH&S, Emp_Continuous_Improvement, Emp_Initiative, Emp_Teamwork, Emp_Time_Management, " _
    & "Emp_Productivity, Emp_Lead_Self, Emp_Inspire_and_Empower, Emp_Achieve_Results, " _
    & "[Emp_Drive_Change_&_Innovation], Emp_Builds_Trust, Emp_Ethics, [Strengths/Achievements], " _
    & "Opportunities_for_Development, Area_Preference_1, Area_Preference_2, Area_Preference_3, " _
    & "Area_Preference_4, Job_Rotation_History, Emp_Assessment, Summary, Supervisor_Comments, " _
    & "Employee_Comments, Emp_Signature_Date, Sup_Signature_Date, [Sup_Expertise/Job_Knowledge], " _
    & "Sup_Quality, Sup_Dependability, Sup_Delivering_Solutions, Sup_EH&S, Sup_Continuous_Improvement, " _
    & "Sup_Initiative, Sup_Teamwork, Sup_Time_Management, Sup_Productivity, Sup_Lead_Self, " _
    & "Sup_Inspire_and_Supower, Sup_Achieve_Results, [Sup_Drive_Change_&_Innovation], " _
    & "Sup_Builds_Trust, Sup_Ethics, Sup_Assessment) VALUES (" _
    & reportRecord & ", " & Badge_ID & ", #" & Format(reviewDate,"yyyy-mm-dd") & "#, '" & progOpt & "', " _
    & avgRating & ", #" & Format(nextReview,"yyyy-mm-dd") & "#, '" & empExpertise & "', '" & empQuality & "', " _
    ' 剩余变量按照实际类型补全对应分隔符，按上述规则自行补全即可
    & "'" & supAssess & "')"
    
    ' 执行SQL，避免弹出操作确认弹窗，同时报错会触发错误提示
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

调试技巧：可以在执行前加Debug.Print strSQL打印拼接后的完整SQL语句，放到Access查询编辑器里直接运行，能快速定位剩余语法问题。

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