如何在MS Access应用中向Excel插入医生记录？VBA SQL报错求助

核心错误点 #

1. 字段名格式错误：所有包含空格、特殊符号（如#）的字段名必须用方括号[]包裹，否则SQL引擎无法识别，比如Last Name要改成[Last Name]，Office #改成[Office #]。
2. 非法常量N/A：SQL中不存在N/A关键字，若要插入空值需用NULL；如果确实要存储字符串"N/A"，必须给它加单引号，写成'N/A'。
3. 字符串拼接风险：直接拼接表单值到SQL语句中，若用户输入包含单引号（如O'Neil）会直接触发语法错误，还存在SQL注入风险。

修复语法错误的基础版本 #

如果是要插入空值（使用NULL）：

Private Sub InsertBtn_Click()
    Dim sqlStr As String
    sqlStr = "INSERT INTO employeelist ([Last Name], [First Name], [Facility], [Specialty], [Office #], [Fax #], [Cell #], [address 1], [Street address], [City], [State], [Zip Code], [Email], [WebPage]) " & _
             "VALUES('" & Replace(Me.LastNameInsert.Value, "'", "''") & "', '" & Replace(Me.FirstNameInsert.Value, "'", "''") & "', NULL, '" & Replace(Me.SpecialtyInsert.Value, "'", "''") & "', NULL, NULL, '" & Replace(Me.CellNumInsert.Value, "'", "''") & "', NULL, NULL, '" & Replace(Me.CityInsert.Value, "'", "''") & "', NULL, NULL, NULL, NULL)"
    DoCmd.RunSQL sqlStr
End Sub

注：这里用Replace函数把输入中的单个单引号替换成双单引号，避免SQL语法报错。

如果是要存储字符串"N/A"：

Private Sub InsertBtn_Click()
    Dim sqlStr As String
    sqlStr = "INSERT INTO employeelist ([Last Name], [First Name], [Facility], [Specialty], [Office #], [Fax #], [Cell #], [address 1], [Street address], [City], [State], [Zip Code], [Email], [WebPage]) " & _
             "VALUES('" & Replace(Me.LastNameInsert.Value, "'", "''") & "', '" & Replace(Me.FirstNameInsert.Value, "'", "''") & "', 'N/A', '" & Replace(Me.SpecialtyInsert.Value, "'", "''") & "', 'N/A', 'N/A', '" & Replace(Me.CellNumInsert.Value, "'", "''") & "', 'N/A', 'N/A', '" & Replace(Me.CityInsert.Value, "'", "''") & "', 'N/A', 'N/A', 'N/A', 'N/A')"
    DoCmd.RunSQL sqlStr
End Sub

更安全的参数化查询版本（推荐） #

完全避免字符串拼接的问题，同时杜绝SQL注入风险：

Private Sub InsertBtn_Click()
    Dim qd As QueryDef
    Dim sqlStr As String
    
    sqlStr = "INSERT INTO employeelist ([Last Name], [First Name], [Facility], [Specialty], [Office #], [Fax #], [Cell #], [address 1], [Street address], [City], [State], [Zip Code], [Email], [WebPage]) " & _
             "VALUES([pLastName], [pFirstName], NULL, [pSpecialty], NULL, NULL, [pCellNum], NULL, NULL, [pCity], NULL, NULL, NULL, NULL)"
    
    Set qd = CurrentDb.CreateQueryDef("", sqlStr)
    ' 绑定表单参数
    qd.Parameters("[pLastName]") = Me.LastNameInsert.Value
    qd.Parameters("[pFirstName]") = Me.FirstNameInsert.Value
    qd.Parameters("[pSpecialty]") = Me.SpecialtyInsert.Value
    qd.Parameters("[pCellNum]") = Me.CellNumInsert.Value
    qd.Parameters("[pCity]") = Me.CityInsert.Value
    
    qd.Execute dbFailOnError ' 执行并强制捕获错误
    Set qd = Nothing
End Sub

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