too few parameters error msg

[amypaulsen]

This is the code I'm using in VB-Excel to pull info out of access:

Set objDB = objAccess.currentdb()
With objDB

strSQL = "select " & QueryName & ".UNIT, " & QueryName & ".CATEGORY, " & QueryName & "." & WeekNum & " from " & QueryName & " where category = '" & CatName & "';"

Debug.Print strSQL

Set rst = objDB.openrecordset(strSQL)

When I run this, I keep erroring out on the last line "Set rst = objDB.openrecordset(strSQL)" stating: too few parameters, expected 1

When I hover over the SQL statement or look in the locals window, it is clearly recognizing what to pull.

What the HECK am I doing wrong????

Please help!

 

[Norie]

Amy

If you can, try copying the strSQL into a new query in the Access DB where you are trying to pull the info from.

There might be something in the Access queries that you are trying to query that is not in you're strSQL, it might be a Group By or Sum.

Look at the SQL of the original queries and see if there is anything that is not in you're strSQL.

 

[SydneyGeek]

Amy, when you run a query in code, any parameters have to be specifically evaluated. Here's what I do in Access:

Dim dbs as DAO.DataBase
Dim rst as DAO.Recordset
Dim qdf as DAO.QueryDef
Dim prm as DAO.Parameter

Set dbs=CurrentDB()
Set qdf=dbs.QueryDefs("YourQueryName")
For each prm in qdf.Parameters
   prm=EVAL(prm.value)
[or it could be, prm.Value=EVAL(prm) -- don't have the code with me at the moment]
Next prm

...open the recordset here

Hope this helps

Denis