使用VBA将Excel列导入Access字段时遇参数缺失错误求助

问题场景 #

需要编写Excel VBA代码，将工作表中NDC11、ProductNameLong、ProductName2列的数据插入Access数据库同名字段，但执行INSERT SQL时触发“未提供一个或多个必填参数”错误，原代码如下：

Sub DataPuller()

Dim refresh
Dim year As String
Dim intColIndex As Integer
Dim strPath As String
Dim strProv As String
Dim strConn As String
Dim Conn As New Connection
Dim rsQry As New Recordset
Dim strQry As String
Dim NDCs As Range
Dim ProductName2s As Range

refresh = MsgBox("Start New Query?", vbYesNo)

If refresh = vbYes Then
    year = Application.InputBox("Which year would you like CMS Utilization Data from:" & vbNewLine & "2019, 2020, 2021, or 2022?", "Input Year")
    If year = "2022" Then
        On Error GoTo Whoa
        strPath = "C:\Users\hgarrison\Documents\PRM DOCS\1Q2022 - 4Q2022 CMS Utilization Database.accdb"
        strProv = "Microsoft.ACE.OLEDB.12.0;"
        strConn = "Provider=" & strProv & "Data Source=" & strPath
        Conn.Open strConn
        strQry = "DELETE * from MB_without_financials"
        Conn.Execute strQry
        Sheets.Add.Name = "Data"
        
        xlFilepath = Application.ThisWorkbook.FullName
        strQry = "INSERT INTO MB_without_financials(NDC11, ProductNameLong, ProductName2) SELECT NDC11, ProductNameLong, ProductName2 FROM [Excel 12.0 xml;HDR=YES;DATABASE=" & xlFilepath & "].[Sheet1$]"
        Conn.Execute strQry


LetsContinue:
       Application.ScreenUpdating = True
       On Error Resume Next
       rs.Close
       Set rs = Nothing
       cn.Close
       Set cn = Nothing
       On Error GoTo 0
       Exit Sub
Whoa:
       MsgBox "Error Description :" & Err.Description & vbCrLf & _
             "Error at line     :" & Erl & vbCrLf & _
             "Error Number      :" & Err.Number
       Resume LetsContinue
    End If

End If

End Sub

错误原因排查 #

1. 字段/列名不匹配：Access表MB_without_financials的字段名与Excel工作表的列名必须完全一致（包括大小写、空格、特殊字符），若存在拼写错误（比如ProductNameLong写成ProductNameLng），会被识别为未定义的参数。
2. Excel工作表引用错误：代码中使用[Sheet1$]，若实际数据所在工作表不是Sheet1，或工作表名包含空格/特殊字符但未正确引用，会导致无法读取列数据，触发参数缺失错误。
3. 隐式变量风险：xlFilepath未声明，VBA隐式声明可能导致路径拼接异常，进而使Excel数据源引用失效。
4. 对象关闭变量不匹配：错误处理段中使用rs、cn，但代码中实际的记录集对象是rsQry、连接对象是Conn，变量名不匹配会导致资源未正确释放，也可能干扰后续操作。
5. Access表存在其他必填字段：若MB_without_financials表除了指定的三个字段外，还有其他设置为“必填”的字段，而INSERT语句未提供这些字段的值，就会触发该错误。

修正后的代码 #

Option Explicit '强制变量声明，避免隐式声明错误

Sub DataPuller()
    Dim refresh As VbMsgBoxResult
    Dim year As String
    Dim strPath As String
    Dim strProv As String
    Dim strConn As String
    Dim Conn As Object '改用后期绑定，避免引用DAO/ADO库问题
    Dim strQry As String
    Dim xlFilepath As String

    refresh = MsgBox("Start New Query?", vbYesNo)

    If refresh = vbYes Then
        year = Application.InputBox("Which year would you like CMS Utilization Data from:" & vbNewLine & "2019, 2020, 2021, or 2022?", "Input Year")
        If year = "2022" Then
            On Error GoTo Whoa
            '初始化连接对象
            Set Conn = CreateObject("ADODB.Connection")
            
            strPath = "C:\Users\hgarrison\Documents\PRM DOCS\1Q2022 - 4Q2022 CMS Utilization Database.accdb"
            strProv = "Microsoft.ACE.OLEDB.12.0"
            strConn = "Provider=" & strProv & ";Data Source=" & strPath
            Conn.Open strConn

            '清空目标表
            strQry = "DELETE * FROM MB_without_financials"
            Conn.Execute strQry

            '注意：此处新增的Data工作表未被使用，若不需要可删除
            Sheets.Add.Name = "Data"

            '获取当前工作簿路径
            xlFilepath = Application.ThisWorkbook.FullName
            '确保工作表引用正确，若Sheet1有空格，改为'[Sheet Name$]'
            strQry = "INSERT INTO MB_without_financials(NDC11, ProductNameLong, ProductName2) " & _
                     "SELECT NDC11, ProductNameLong, ProductName2 " & _
                     "FROM [Excel 12.0 Xml;HDR=YES;Database=" & xlFilepath & "].[Sheet1$]"
            Conn.Execute strQry

LetsContinue:
            '正确关闭连接对象
            On Error Resume Next
            If Not Conn Is Nothing Then
                Conn.Close
                Set Conn = Nothing
            End If
            On Error GoTo 0
            Exit Sub

Whoa:
            MsgBox "Error Description :" & Err.Description & vbCrLf & _
                  "Error at line     :" & Erl & vbCrLf & _
                  "Error Number      :" & Err.Number
            Resume LetsContinue
        End If
    End If
End Sub

额外检查项 #

• 打开Access数据库，确认MB_without_financials表的NDC11、ProductNameLong、ProductName2字段名与Excel列名完全一致，且无其他必填字段（若有，需在INSERT语句中补充对应值，或修改字段的必填属性）。
• 确认Excel的Sheet1中确实存在这三列，且表头行（HDR=YES指定的第一行）的列名准确。
• 若Excel文件路径包含空格，需确保连接字符串中的路径被正确解析，或改用短路径。

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