MS Access表单插入数据时触发Runtime error 3134错误求助

问题排查与修复步骤 #

• 带空格的表名/字段未用方括号包裹：Access中，包含空格的表名、字段名必须用[]括起来，否则SQL解析会报错。比如原代码里的Main Sheet要改为[Main Sheet]，所有带空格的字段（如Student Name）都要加上[]。
• VALUES子句第一个值缺失单引号：原代码中VALUES(" & Me.stdName & "','"部分，第一个字段值Me.stdName前没有单引号，直接导致SQL结构断裂。
• 日期时间字段格式错误：Pick Up Time属于日期时间类型，Access中需用#而非单引号包裹，否则会被识别为字符串，引发语法或类型不匹配问题。
• SQL拼接的潜在风险：直接拼接表单控件值到SQL中，不仅容易出语法错误，还存在SQL注入风险，建议改用参数化查询。

修复后的代码示例 #

方式1：修正后的SQL拼接代码 #

CurrentDb.Execute "INSERT INTO [Main Sheet]([Student Name], [Pick Up Time], [Home Address], [Home City], [School Hours], [School Name], [School Address], [School City], [Notes], [School District], [Parent Tel], [Emergency Contact], [Driver])" & _
    "VALUES('" & Me.stdName & "',#" & Me.pickUpTime & "#,'" & Me.homeAddress & "','" & Me.homeCity & "','" & Me.schoolHours & "','" & Me.schoolName & "','" & Me.schoolAddress & "','" & Me.schoolCity & "','" & Me.notes & "','" & Me.schoolDistrict & "','" & Me.parentTel & "','" & Me.emergencyContact & "','" & Me.driver & "')"
' 刷新表单列表数据
MainForm.Form.Requery

注意：如果字段值包含单引号（如学生名为O'Neil），这种拼接方式仍会出错，推荐使用参数化查询。

方式2：更安全的参数化查询（推荐） #

Dim qd As QueryDef
Set qd = CurrentDb.CreateQueryDef("", _
    "INSERT INTO [Main Sheet]([Student Name], [Pick Up Time], [Home Address], [Home City], [School Hours], [School Name], [School Address], [School City], [Notes], [School District], [Parent Tel], [Emergency Contact], [Driver])" & _
    "VALUES(@stdName, @pickUpTime, @homeAddress, @homeCity, @schoolHours, @schoolName, @schoolAddress, @schoolCity, @notes, @schoolDistrict, @parentTel, @emergencyContact, @driver)")

' 绑定参数值
qd.Parameters("@stdName") = Me.stdName
qd.Parameters("@pickUpTime") = Me.pickUpTime
qd.Parameters("@homeAddress") = Me.homeAddress
qd.Parameters("@homeCity") = Me.homeCity
qd.Parameters("@schoolHours") = Me.schoolHours
qd.Parameters("@schoolName") = Me.schoolName
qd.Parameters("@schoolAddress") = Me.schoolAddress
qd.Parameters("@schoolCity") = Me.schoolCity
qd.Parameters("@notes") = Me.notes
qd.Parameters("@schoolDistrict") = Me.schoolDistrict
qd.Parameters("@parentTel") = Me.parentTel
qd.Parameters("@emergencyContact") = Me.emergencyContact
qd.Parameters("@driver") = Me.driver

' 执行查询
qd.Execute dbFailOnError

' 释放对象
Set qd = Nothing

' 刷新表单数据
MainForm.Form.Requery

参数化查询能自动处理特殊字符，避免语法错误，同时杜绝SQL注入风险，是更可靠的写法。

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