Parameter Query call failing with ADODB

pbassett

Active Member
Joined
May 5, 2004
Messages
358
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pbassett

Active Member
Joined
May 5, 2004
Messages
358
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,727
Messages
5,524,477
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top