MS Access录入含单引号的记录报错，求解决方案

你的问题确实是单引号未转义导致的SQL语法错误。当歌曲标题包含单引号（比如"We're Going On"）时，直接拼接SQL语句会让SQL引擎把文本里的单引号误判为字符串结束标记，进而引发语法错误。

方法1：转义单引号（快速修复） #

在Access SQL里，用两个连续单引号表示一个转义的单引号。只需修改构建strSQL1的代码，把输入文本中的单个单引号替换成两个：

strSQL1 = "Insert Into tblClick ([SongTitle]) " & _
          "values ('" & Replace(NewData, "'", "''") & "');"

修改后的完整代码：

Private Sub SongTitle_NotInList(NewData As String, Response As Integer)
    On Error GoTo SongTitle_NotInList_Error
    Dim strSQL1 As String
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "This is a new Song Title...")
    If i = vbYes Then
        ' 转义单引号
        strSQL1 = "Insert Into tblClick ([SongTitle]) " & _
                  "values ('" & Replace(NewData, "'", "''") & "');"

        CurrentDb.Execute strSQL1, dbFailOnError
        Response = acDataErrAdded
    End If

    On Error GoTo 0
    Exit Sub

SongTitle_NotInList_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SongTitle_NotInList, line " & Erl & "."
End Sub

方法2：使用参数化查询（更安全规范） #

如果想避免手动转义，同时防范SQL注入风险，推荐用参数化查询处理：

Private Sub SongTitle_NotInList(NewData As String, Response As Integer)
    On Error GoTo SongTitle_NotInList_Error
    Dim qdf As QueryDef
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "This is a new Song Title...")
    If i = vbYes Then
        ' 创建参数化查询
        Set qdf = CurrentDb.CreateQueryDef("", _
            "Insert Into tblClick ([SongTitle]) values ([NewTitle]);")
        ' 给参数赋值
        qdf.Parameters("NewTitle") = NewData
        ' 执行查询
        qdf.Execute dbFailOnError
        ' 释放对象
        Set qdf = Nothing
        
        Response = acDataErrAdded
    End If

    On Error GoTo 0
    Exit Sub

SongTitle_NotInList_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SongTitle_NotInList, line " & Erl & "."
End Sub

参数化查询会自动处理所有特殊字符，无需手动转义，同时更适合处理用户输入类场景，安全性更高。

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