SQL and/or DAO question

[AdaHacker]

This is probably really easy, and may have been answered before, but I can't seem to find it.

How do I pass a parameter to a stored query in an Access database? I'm building a different query in code, and it joins a table to a stored query that requires a parameter. I tried using the QueryDefs.Parameters collection in DAO to set it, but when I do my Database.OpenRecordset with the SQL I built in code, I just get an error saying I didn't pass it enough parameters.

I'm probably missing something obvious, I just don't know what. Anyone have any ideas?

 

[EnglishMan]

Please post code. It makes solving problems easier.

 

[Brewman76]

Create a temporary QueryDef using the SQL you built from code. Then use the Parameters collection of the QueryDef object and execute the querydef.

Say I have a query "qryFirstQuery"
and I have an SQL statement that joins a table with qryFirstQuery stored in a string variable called "strSQL".

dim db as Database
dim objQuery as QueryDef

set db = '''' open your database
set objQuery = db.CreateQueryDef("",strSQL)
'leaving the query name blank makes it temporary
objQuery.Parameters("MyParam&quot.Value = MyValue
objQuery.Execute

 

[AdaHacker]

AdamMensch,
Thanks. That works quite well.