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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,029
Messages
5,856,933
Members
431,840
Latest member
simplysoph

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top