ACCESS中Insert Into批量插入异常，请求代码修正

问题现象 #

使用CurrentDb.Execute执行INSERT INTO语句时遇到两个问题：

1. 单条语句批量插入大量字段值时无法成功执行；
2. 拆分为多条CurrentDb.Execute语句后，仅第一条能正常执行，后续语句全部失效。

原代码核心错误 #

1. 第一条语句语法错误：字符串拼接时出现多余逗号，且字段数量与对应值的数量不匹配，导致SQL语句非法；
2. 后续语句逻辑错误：每条后续语句都执行新的INSERT操作，会向表中插入仅填充部分字段的空行，而非更新同一行的剩余字段；同时语句末尾多余& "，导致SQL不完整；
3. SQL注入风险：直接拼接表单控件值到SQL语句，若值包含单引号会导致语句崩溃，且存在安全隐患。

修复后的代码 #

采用参数化查询构建SQL语句，既解决语法问题，又避免SQL注入，同时确保所有字段一次性插入到同一行：

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sqlStr As String

Set db = CurrentDb

' 构建带参数占位符的完整INSERT语句
sqlStr = "INSERT INTO Data (" & _
    "[Region],[Office],[Employee Name],[Role],[Email ID]," & _
    "[T1 - New File Creation],[T1 - Roll forward],[T1 - Final package (PDF)],[T1 - Final package (paper)]," & _
    "[T1 - Efiling],[T1 - paper filing],[T1 - pre-season package (PDF)],[T1 -pre-season package (paper)]," & _
    "[T1 -FROR Checklist DESC Service check],[T1 - DT1 Intake and Initiate],[T1 - Scan and validate]," & _
    "[T1 - CRA website document downloads (Pulling NOA and other tax)]," & _
    "[T2 -New file creation],[T2 -Roll forward],[T2 -Final package (PDF)],[T2 -Final package (paper)]," & _
    "[T2 -Efiling],[T2 - Returns]," & _
    "[T3 - New file creation],[T3 - Roll forward],[T3 -Final package (PDF)],[T3 -Final package (paper)]," & _
    "[T3 - Net filing],[T3 -Paper filing]," & _
    "[T4_T5 - New file creation],[T4_T5 - Roll forward],[T4_T5 - Final package (PDF)],[T4_T5 -Final package (paper)]," & _
    "[T4_T5 - Netfiling],[T4_T5 - Paper filing]," & _
    "[T3010 - New file creation],[T3010 -Roll forward],[T3010 - Final package (PDF)],[T3010 - Final package (paper)]," & _
    "[T3010 - Paper filing]," & _
    "[US tax -Letters(templafy & ms word)],[US tax -Printing/assembly(Dti)],[US tax - Printing/assembly(PDF)]," & _
    "[US tax  - Printing/assembly(paper)],[Us tax - Paper filing]," & _
    "[USPT returns -Scan client information to pdf],[USPT returns -Printing/Assembly (CCH Axcess)]," & _
    "[USPT returns- Printing/Assembly (paper)],[USPT returns -Efiling],[USPT returns -Paper filing]," & _
    "[GES tax - Finalize and mail],[GES tax - Finalize and pdf],[GES tax - Scanning],[O&E submissions]," & _
    "[EL Draft],[EL Finalize]) " & _
    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

' 创建查询定义
Set qdf = db.CreateQueryDef("", sqlStr)

' 按字段顺序绑定参数值（需确保控件名称与实际表单一致）
qdf.Parameters(0) = Me.Region.Value
qdf.Parameters(1) = Me.Office.Value
qdf.Parameters(2) = Me.Employee_Name.Value
qdf.Parameters(3) = Me.Role.Value
qdf.Parameters(4) = Me.Email_ID.Value
qdf.Parameters(5) = Me.T1___New_File_Creation.Value
qdf.Parameters(6) = Me.T1___Roll_forward.Value
qdf.Parameters(7) = Me.T1___Final_package__PDF_.Value
qdf.Parameters(8) = Me.T1___Final_package__paper_.Value
qdf.Parameters(9) = Me.T1___Efiling.Value
qdf.Parameters(10) = Me.T1___paper_filing.Value
qdf.Parameters(11) = Me.T1___pre_season_package__PDF_.Value
qdf.Parameters(12) = Me.T1__pre_season_package__paper_.Value
qdf.Parameters(13) = Me.T1__FROR_Checklist_DESC_Service_check.Value
qdf.Parameters(14) = Me.T1___DT1_Intake_and_Initiate.Value
qdf.Parameters(15) = Me.T1___Scan_and_validate.Value
qdf.Parameters(16) = Me.T1___CRA_website_document_downloads__Pulling_NOA_and_other_tax_.Value
qdf.Parameters(17) = Me.T2__New_file_creation.Value
qdf.Parameters(18) = Me.T2__Roll_forward.Value
qdf.Parameters(19) = Me.T2__Final_package__PDF_.Value
qdf.Parameters(20) = Me.T2__Final_package__paper_.Value
qdf.Parameters(21) = Me.T2__Efiling.Value
qdf.Parameters(22) = Me.T2___Returns.Value
qdf.Parameters(23) = Me.T3___New_file_creation.Value
qdf.Parameters(24) = Me.T3___Roll_forward.Value
qdf.Parameters(25) = Me.T3__Final_package__PDF_.Value
qdf.Parameters(26) = Me.T3__Final_package__paper_.Value
qdf.Parameters(27) = Me.T3___Net_filing.Value
qdf.Parameters(28) = Me.T3__Paper_filing.Value
qdf.Parameters(29) = Me.T4_T5___New_file_creation.Value
qdf.Parameters(30) = Me.T4_T5___Roll_forward.Value
qdf.Parameters(31) = Me.T4_T5___Final_package__PDF_.Value
qdf.Parameters(32) = Me.T4_T5__Final_package__paper_.Value
qdf.Parameters(33) = Me.T4_T5___Netfiling.Value
qdf.Parameters(34) = Me.T4_T5___Paper_filing.Value
qdf.Parameters(35) = Me.T3010___New_file_creation.Value
qdf.Parameters(36) = Me.T3010__Roll_forward.Value
qdf.Parameters(37) = Me.T3010___Final_package__PDF_.Value
qdf.Parameters(38) = Me.T3010___Final_package__paper_.Value
qdf.Parameters(39) = Me.T3010___Paper_filing.Value
qdf.Parameters(40) = Me.US_tax__Letters_templafy___ms_word_.Value
qdf.Parameters(41) = Me.US_tax__Printing_assembly_Dti_.Value
qdf.Parameters(42) = Me.US_tax___Printing_assembly_PDF_.Value
qdf.Parameters(43) = Me.US_tax____Printing_assembly_paper_.Value
qdf.Parameters(44) = Me.Us_tax___Paper_filing.Value
qdf.Parameters(45) = Me.USPT_returns__Scan_client_information_to_pdf.Value
qdf.Parameters(46) = Me.USPT_returns__Printing_Assembly__CCH_Axcess_.Value
qdf.Parameters(47) = Me.USPT_returns__Printing_Assembly__paper_.Value
qdf.Parameters(48) = Me.USPT_returns__Efiling.Value
qdf.Parameters(49) = Me.USPT_returns__Paper_filing.Value
qdf.Parameters(50) = Me.GES_tax___Finalize_and_mail.Value
qdf.Parameters(51) = Me.GES_tax___Finalize_and_pdf.Value
qdf.Parameters(52) = Me.GES_tax___Scanning.Value
qdf.Parameters(53) = Me.O_E_submissions.Value
qdf.Parameters(54) = Me.EL_Draft.Value
qdf.Parameters(55) = Me.EL_Finalize.Value

' 执行查询，失败时抛出错误便于排查
qdf.Execute dbFailOnError

' 释放对象
Set qdf = Nothing
Set db = Nothing

关键修复说明 #

1. 合并为单条INSERT语句：将所有字段整合到一个语句中，确保所有数据插入到同一行，避免拆分语句导致的多行无效插入；
2. 参数化查询：用?作为占位符绑定控件值，彻底解决字符串拼接的语法错误和SQL注入风险；
3. 字段值匹配修正：补充原代码遗漏的字段对应值，确保每个字段都有匹配的输入值；
4. 错误处理：添加dbFailOnError参数，执行失败时直接抛出错误，方便定位问题。

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