如何为ListObject QueryTable传参？VBA连接Access查询遇阻

我正尝试用VBA自动化建立与Access嵌套查询的数据链接，无法像往常一样用SQL语句替代数据源。录制宏并整理代码后，将命令文本指向目标查询PeramIndvCallScore，但始终无法正确添加日期参数，已搜索两天仍对语法困惑。

现有代码 #

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[]"), _
        Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("PeramIndvCallScore")
        '.Parameters(1).SetParam xlConstant, "8/1/2022"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = []
        .Refresh BackgroundQuery:=False
    End With

错误情况 #

运行时会出现多种错误：

• “查询未运行”
• “应用程序/对象定义错误”
• “对象不支持该属性或方法”（移除Parameters后的(1)时出现）

需求 #

1. 能用单元格区域作为参数实现动态数据链接
2. 能否让ADO Recordset像数据链接一样自动刷新？
3. 代码中所有属性是否必要，未指定的属性是否有默认值？

目标查询PeramIndvCallScore的SQL语句 #

SELECT CallDetails.CallDate, IIf(([CallTypes].[ID]=1) Or ([calltypes].[ID]=4) Or ([calltypes].[ID]=6),'CSB/VES/Govt',[CallTypes].[Type]) AS CallType, CalculatedScores.TotalScore
FROM CallTypes INNER JOIN (CalculatedScores INNER JOIN CallDetails ON CalculatedScores.KeyID = CallDetails.KeyID) ON CallTypes.ID = CallDetails.CallType
WHERE (((CallDetails.CallDate)=[Date]) AND ((IIf(([CallTypes].[ID]=1) Or ([calltypes].[ID]=4) Or ([calltypes].[ID]=6),'CSB/VES/Govt',[CallTypes].[Type]))='CSB/VES/Govt') AND ((CallDetails.Spanish)=False) AND ((CallDetails.Omit)=False))
UNION ALL SELECT CallDetails.CallDate, IIf(([Calldetails].[Spanish]),'Spanish','') AS CallType, CalculatedScores.TotalScore
FROM CalculatedScores INNER JOIN CallDetails ON CalculatedScores.KeyID = CallDetails.KeyID
WHERE (((CallDetails.CallDate)=[Date]) AND ((IIf(([Calldetails].[Spanish]),'Spanish',''))="CSB/VES/Govt") AND ((CallDetails.Spanish)=True) AND ((CallDetails.Omit)=False));

我注意到SQL中可直接输入日期变量，但后续项目仍需掌握为QueryTable添加参数的方法，恳请提供帮助。

一、修复QueryTable参数传递问题 #

核心问题是CommandType设置错误，以及参数初始化方式不对。Access参数化查询需用xlCmdSql而非xlCmdTable，并正确初始化参数集合：

修改后的代码（支持单元格参数） #

Sub RefreshAccessParamQuery()
    Dim qt As QueryTable
    Dim ws As Worksheet
    Dim dateParamRange As Range
    
    ' 定义参数所在单元格（示例为A10）
    Set ws = ActiveSheet
    Set dateParamRange = ws.Range("A10")
    
    ' 清理已存在的QueryTable（避免重复创建）
    On Error Resume Next
    ws.ListObjects("Table_PeramIndvCallScore").Delete
    On Error GoTo 0
    
    ' 创建新QueryTable
    Set qt = ws.ListObjects.Add(SourceType:=0, _
        Source:="OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=你的Access文件路径", _
        Destination:=ws.Range("$A$1")).QueryTable
    
    With qt
        .CommandType = xlCmdSql
        ' 用EXEC调用Access参数化查询，?为参数占位符
        .CommandText = "EXEC PeramIndvCallScore ?"
        ' 绑定参数到指定单元格
        .Parameters.Add _
            Name:="DateParam", _
            Type:=xlParamTypeDate, _
            ParameterType:=xlInput, _
            SourceRange:=dateParamRange
        ' 保留必要属性（其余用默认值）
        .PreserveFormatting = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .PreserveColumnInfo = True
        ' 刷新数据
        .Refresh BackgroundQuery:=False
    End With
End Sub

关键说明： #

1. CommandType改为xlCmdSql：执行带参数的查询而非直接读取表，必须使用该类型。
2. EXEC调用查询：Access OLEDB支持EXEC 查询名 参数的语法传递参数，?是占位符。
3. 单元格绑定：通过SourceRange直接关联参数单元格，单元格内容变化后，刷新QueryTable会自动读取新值。

二、ADO Recordset自动刷新的实现方式 #

ADO Recordset本身无自动刷新机制，但可通过以下两种方式模拟：

1. 监听单元格变化触发刷新：利用Worksheet_Change事件，当参数单元格修改时自动重新查询。
2. 定时刷新：用Application.OnTime设置定时任务，周期性执行刷新逻辑。

示例（监听单元格变化） #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 当参数单元格（示例A10）变化时触发刷新
    If Not Intersect(Target, Me.Range("A10")) Is Nothing Then
        RefreshADORecordset
    End If
End Sub

Sub RefreshADORecordset()
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim dateParam As Date
    
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    dateParam = Me.Range("A10").Value
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=你的Access文件路径"
    
    ' 执行参数化SQL查询
    sql = "SELECT * FROM PeramIndvCallScore WHERE [Date] = ?"
    rs.Open sql, conn, 1, 3, Array(Array(dateParam, 7)) ' 7对应ADODB的adDate类型
    
    ' 清空旧数据并写入新结果
    Me.Range("A1").CurrentRegion.ClearContents
    Me.Range("A1").CopyFromRecordset rs
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

三、QueryTable属性必要性说明 #

原代码中多数属性可省略，Excel会使用默认值：

• 可省略属性：RowNumbers（默认False）、FillAdjacentFormulas（默认False）、RefreshOnFileOpen（默认False）、BackgroundQuery（默认True）、SavePassword（默认False）、RefreshPeriod（默认0）。
• 建议保留属性：PreserveFormatting、RefreshStyle、SaveData、AdjustColumnWidth、PreserveColumnInfo——这些直接影响数据导入后的格式和更新行为。

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