Search code examples
vbams-accessdao

VBA add parameters to a new queryDef

This code works fine: 

Dim db As DAO.Database, rs As DAO.Recordset, qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("query1")
qd.Parameters("[cou]").Value = "BE"
Set rs = qd.OpenRecordset
Do Until rs.EOF
    Debug.Print rs!title, rs!country_fk
    rs.MoveNext
Loop
rs.Close

But when I try to achieve the same result by CREATING a querydef instead of using an existing one, I get an error on the qd.Parameters line.

Set db = CurrentDb
Set qd = db.CreateQueryDef
qd.SQL = "PARAMETERS [cou] Text ( 255 ); SELECT TOP 10 Title, Country_fk FROM dbo_Client WHERE Country_fk=[cou];"
qd.Parameters("[cou]").Value = "BE"

Set rs = qd.OpenRecordset
Do Until rs.EOF
...

I noticed that qd.Parameters.Count = 0 and that qd.Parameters.Add is not allowed.
Any solution ? Thx

Solution

  • Your second example should work if you give the QueryDef a name. If you want it to be a temporary QueryDef, use an empty string for the name ...

    'Set qd = db.CreateQueryDef
Set qd = db.CreateQueryDef(vbNullString)