Passing Parameters into a ListObject QueryTable

Question

(CrossPosted from Reddit) I'm trying to use VBA to automate a data link to an Access Query. Unfortunately the query is a query of a query, so I'm not able to substitute a nice descriptive SQL statement for the source, like I normally would :/ I recorded a Macro of linking a different query to see what it would look like, then cleaned up the source and changed the command text to reference the query I'm wanting, but I've been metaphorically smashing my keyboard trying to understand how to add parameters. All I need is a date. I've been googling for 2 days now, and the syntax has me all kinds of confused.

Here's what I've got, with my most recent attempt commented out and the file path replaced with brackets

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

I'll end up with different Run-time errors between "the query did not run," "application/object-defined error," "Object doesn't support this property or method," (when I remove the (1) from parameters)

Ideally I want to be able to use a cell range for parameters so as to make a dynamic data link.

Does anyone know if I can set an ADO Recordset to auto refresh in the same way that a data link does? Also, are all the properties here necessary, or can I omit a few of them? (In that, if I don't specify, do they have a default they're set to?)

Here's the SQL:

PeramIndvCallScore:

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));

And I've just taken a second look and saw that there is a spot that I can input the date as a variable in the SQL. Regardless, I will need to know how to add parameters to a QueryTable in the future for other parts of this project. Any help is appreciated :)

Answer 1

Several issues regarding your implementation:

• SetParam is a property of QueryTable.Parameter object. Therefore, consider nesting another With block to initialize QueryTable.Parameter and call SetParam.

• Though not visibly documented, parameters appear to be supported with ODBC Driver and not OLEDB Provider connections. Therefore, adjust to use the MS Access ODBC driver.

• To use parameters, qmarks, ?, must be used for parameter placeholders and not named identifiers (which prompts user to enter value inside the MS Access GUI).

• The XlCmdType does not support saved queries or views of backend database. Therefore, use the xlCmdSql type and pass an SQL string either from a cell range or .sql file. Below shows .sql file approach.

SQL

Save below as .sql file. Query uses table aliases and IN to tighten up code.

SELECT cd.CallDate, 
       IIf((ct.[ID] IN (1, 4 6), 'CSB/VES/Govt', ct.[Type]) AS CallType, 
       cs.TotalScore
FROM CallTypes ct
INNER JOIN (CalculatedScores cs
INNER JOIN CallDetails cd ON cs.KeyID = cd.KeyID) 
  ON ct.ID = cd.CallType
WHERE (((cd.CallDate) = ?) 
  AND ((IIf((ct.[ID] IN (1, 4 6), 'CSB/VES/Govt', ct.[Type])) = 'CSB/VES/Govt') 
  AND ((cd.Spanish)=False) AND ((cd.Omit)=False))

UNION ALL

SELECT cd.CallDate, 
       IIf(([Calldetails].[Spanish]), 'Spanish', '') AS CallType, 
       cs.TotalScore
FROM CalculatedScores cs
INNER JOIN CallDetails cd ON cs.KeyID = cd.KeyID
WHERE (((cd.CallDate) = ?) 
  AND ((IIf(([Calldetails].[Spanish]), 'Spanish', '')) = 'CSB/VES/Govt') 
  AND ((cd.Spanish)=True) AND ((cd.Omit)=False));

VBA

Below reads in .sql file, explicitly qualify workbook objects without Active calls, and incorporates error handling. Please adjust file paths and worksheet numbers accordingly.

Sub BuildQueryTable()
On Error GoTo ErrHandle
    Dim strSQL As String
    
    ' READ .SQL INTO A STRING VARIABLE
    With CreateObject("Scripting.FileSystemObject")
          strSQL = .OpenTextFile("C:\Path\To\My\Query.sql", 1).readall
    End With

    ' CREATE LIST OBJECT
    ThisWorkbook.Worksheets(1).Columns("A:Z").Delete
    
    With ThisWorkbook.Worksheets(1).ListObjects.Add(SourceType:=0, Source:=Array( _
            "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};" _
              & "DBQ=C:\Path\To\My\Database.accdb"), _
            Destination:=ThisWorkbook.Worksheets(1).Range("A1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = strSQL
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True

        ' ADD 1ST PARAMETER AND SET VALUE
        With .Parameters.Add("prmDate1", xlParamTypeDate)
           .SetParam xlConstant, CDate("8/1/2021")
        End With

        ' ADD 2ND PARAMETER AND SET VALUE
        With .Parameters.Add("prmDate2", xlParamTypeDate)
           .SetParam xlConstant, CDate("8/1/2021")
        End With

        'REFRESH QUERY TABLE
        .Refresh BackgroundQuery:=False
    End With
    
ExitHandle:
    Exit Sub
    
ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Resume ExitHandle
End Sub