Parameter Query call failing with ADODB

[pbassett]

My Excel 2007 call to an Access 2007 query that prompts for an academic Term is failing on the rs.Open command below. I need to pass a parameter to the query but the error says it expects the command to be "SELECT, INSERT, etc.". Probably a very simple error.


Dim Queries As Variant
Dim i As Integer
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prmTerm As ADODB.Parameter

Queries = Array("qry1") ' Prompts for Term

Call GetTerm ' Get Term parameter

Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & "myTemplate.xls")

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\myDB.accdb" & ";Persist Security Info=False"

For i = LBound(Queries) To UBound(Queries) ' loop through queries and write results to Management file
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
Sheets(i + 1).Select
.CommandText = Queries(i)
' .CommandType = adCmdStoredProc
' .Parameters.Refresh
Select Case i
Case 0 ' query Prompts for Term
Set prmTerm = cmd.CreateParameter
prmTerm.Type = adChar
prmTerm.Size = Len(Term)
cmd.Parameters.Append prmTerm
cmd.Parameters(0).Value = Term
End Select
Set .ActiveConnection = cn
rs.Open cmd <=== ERROR
End With

 

[pbassett]

I found the error. The query elements in the array were not bracketed with "[" and "] which caused an error in the CommandText cmd element since it puts an "exec" before the query name.