Open Dynamic Parameterized Query using DAO

[ItsMe]

Here is an example of using DAO and QueryDef the long way. I am going to include this here as reference.

I got this example from Microsoft here
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qfd = dbs.QueryDefs("qryMyParameterQuery")

'Supply the parameter value
qdf.Parameters("EnterStartDate") = Date
qdf.Parameters("EnterEndDate") = Date + 7

'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()

I am posting it here because it helps to show what the function that Orange linked to does. Only, the function incorporates the Eval() function to keep things dynamic. I just wanted to give it a mention before I mark the thread Solved.



My vote for the best approach is to incorporate the Eval function within the SQL in the Query object. I don't see there being a performance benefit to writing the extra VBA and using QueryDefs.

Thanks to all that participated!

[orange]

ItsMe,

Found another link on this subject.

[ItsMe]

The OP in that thread ended up using CreateParameter via ADO. I don't know if that is a substitute for using the Eval() function. Looking at the name of the method, one would think it is creating a new Parameter.

From Microsoft.
http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

This method does not automatically append the Parameter object to the Parameters collection of a Command object. This lets you set additional properties whose values ADO will validate when you append the Parameter object to the collection.

I am going to interpret that as the method will validate the properties assigned before creating the parameter? Here Microsoft says the method creates a parameter.

FRom Microsoft

Use the CreateParameter method to create a new Parameter object with a specified name, type, direction, size, and value. Any values you pass in the arguments are written to the corresponding Parameter properties.

My impresion is the CreateParameter method will allow you to create a parameter but not pass a variable to an existing parameter. Having said that, there is an argument available in the method to pass a value to your new parameter.

So this may be a good option to avoid creating Dynamic Parameterized Query Objects and use the CreateParameter method to parameterize a plain Jane SELECT query Object on the fly.

One would have to try it out and see. It does seem to be an efficient approach in terms of its use of system resources.