将Excel单元格文本插入Access数据库时出现INSERT INTO语句语法错误

刚接触VBA编程，这可能是个基础问题，但我花了大量时间找解决方案都没结果，希望能得到帮助。每次尝试把Excel工作表单元格文本插入Access数据库指定表时，都会触发运行时错误-2147217900 (80040e14)。用用户窗体里的TextBox、ComboBox字符串插入是正常的，但插入单元格文本就会报这个语法错误。错误出在insert_ECS_ITM语句里标记为“ERROR”的行。我试了多种字符组合：

• item
• +item+
• '"+item+"'
• '"&item&"'
• "item"
• 'item'
• &item&
  都没法解决问题。

以下是我的代码：

Private Sub MI_ITM_AddItem_CommandButton_Click()

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

db_path = "X:\SPET_DB.accdb"

Set cnt = New ADODB.Connection

db_str = "provider=Microsoft.ACE.OLEDB.12.0; data source=" & db_path

cnt.Open (db_str)

'If (IsNull(DLookup("Drawing", "02_DRAWING", "Drawing='" & MI_DWG_Drawing_TextBox1 & "'"))) Then

'insert_ITM1 = "insert into 05_REQUESTED_ITEM(" _
& "SPET_ID," _
& "Description," _
& "Manifacturer," _
& "Application)" _
& "values(" _
& "'" & MI_ID_CaseID_CUSTOMER_ComboBox & "' & '-' & '" & MI_ID_CaseID_YEAR_TextBox & "' & '-' & '" & MI_ID_CaseID_SEQUENTIAL_TextBox & "'," _
& "'" & MI_ITM_Description_ComboBox & "'," _
& "'" & MI_ITM_Manifacturer_ComboBox & "'," _
& "'" & MI_ITM_Application_ComboBox & "')"

insert_ITM1 = "insert into 05_REQUESTED_ITEM(" _
& "SPET_ID," _
& "Description," _
& "Manifacturer," _
& "Quantity," _
& "Application)" _
& "values(" _
& "'" & MI_ID_CaseID_CUSTOMER_ComboBox & "' & '-' & '" & MI_ID_CaseID_YEAR_TextBox & "' & '-' & '" & MI_ID_CaseID_SEQUENTIAL_TextBox & "'," _
& "'" & MI_ITM_Description_ComboBox & "'," _
& "'" & MI_ITM_Manifacturer_ComboBox & "'," _
& "'" & MI_ITM_Quantity_TextBox.Value & "'," _
& "'" & MI_ITM_Application_ComboBox & "')"
    
cnt.Execute (insert_ITM1)

insert_ITM2 = "insert into 06_ITEM_DETAILS(" _
& "SPET_ID," _
& "Item," _
& "Material_RFQ," _
& "Dimensions," _
& "Component, Part)" _
& "values(" _
& "'" & MI_ID_CaseID_CUSTOMER_ComboBox & "' & '-' & '" & MI_ID_CaseID_YEAR_TextBox & "' & '-' & '" & MI_ID_CaseID_SEQUENTIAL_TextBox & "'," _
& "'" & MI_ITM_Description_ComboBox & "'," _
& "'" & MI_ITM_Material_ComboBox & "'," _
& "'" & MI_ITM_Dimensions_TextBox & "'," _
& "" & MI_ITM_Category_COMPONENT_CheckBox.Value & "," & MI_ITM_Category_PART_CheckBox.Value & ")"
    
cnt.Execute (insert_ITM2)

Dim item As String

For x = 0 To MI_ITM_SelectECS_ListBox.ListCount - 1
    
    If MI_ITM_SelectECS_ListBox.Selected(x) = True Then
        
        item = ThisWorkbook.Worksheets("DWG_ECS").Cells(x + 1, 2).Text
                
        insert_ECS_ITM = "insert into 00_ECS_ITM(" _
        & "ECS," _
        & "Item," _
        & "values(" _
        & " item ," _                                '<-- ERROR TRIGGER !
        & "'" & MI_ITM_Description_ComboBox & "')"
        
        cnt.Execute (insert_ECS_ITM)                 '<-- ERROR !
    
    End If
Next x

'End If

MsgBox "ITEM INSERTED"

Set cnt = Nothing

End Sub

核心错误点 #

你的insert_ECS_ITM语句存在3个致命语法错误：

1. 字段列表格式错误：字段Item后面多了逗号，且字段列表的右括号缺失，导致SQL结构混乱
2. 变量拼接错误：单元格文本变量item没有正确嵌入SQL字符串，字符串类型字段需要用单引号包裹，同时用VBA的&完成拼接
3. SQL语句结构不完整：values(前没有闭合字段列表的括号

修正后的代码片段 #

把出错的循环部分替换为以下代码：

Dim item As String

For x = 0 To MI_ITM_SelectECS_ListBox.ListCount - 1
    
    If MI_ITM_SelectECS_ListBox.Selected(x) = True Then
        
        item = ThisWorkbook.Worksheets("DWG_ECS").Cells(x + 1, 2).Text
        ' 修正SQL语句的结构和变量拼接
        insert_ECS_ITM = "insert into 00_ECS_ITM(" _
        & "ECS," _
        & "Item)" _ ' 去掉多余逗号，添加字段列表的右括号
        & " values(" _
        & "'" & Replace(item, "'", "''") & "'," _ ' 用单引号包裹字符串，同时转义内容里的单引号
        & "'" & Replace(MI_ITM_Description_ComboBox.Value, "'", "''") & "')"
        
        cnt.Execute insert_ECS_ITM
    
    End If
Next x

额外建议 #

1. 转义单引号：如果单元格或控件文本里包含单引号（比如O'Neil），直接拼接会导致SQL语法错误，用Replace(item, "'", "''")把单个单引号替换成双单引号，避免报错
2. 使用参数化查询：直接拼接字符串不仅容易出错，还存在SQL注入风险，推荐用ADODB.Command实现参数化插入，示例如下：

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnt
cmd.CommandText = "insert into 00_ECS_ITM(ECS, Item) values (?, ?)"

For x = 0 To MI_ITM_SelectECS_ListBox.ListCount - 1
    If MI_ITM_SelectECS_ListBox.Selected(x) = True Then
        item = ThisWorkbook.Worksheets("DWG_ECS").Cells(x + 1, 2).Text
        ' 设置参数值
        cmd.Parameters(0).Value = item
        cmd.Parameters(1).Value = MI_ITM_Description_ComboBox.Value
        cmd.Execute
    End If
Next x
Set cmd = Nothing

3. 检查其他SQL语句：你的insert_ITM2语句里，Component, Part字段的赋值没有加单引号（如果是布尔/数字类型没问题，但如果是字符串类型需要补充），建议统一检查所有SQL拼接的格式

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