Unable to pass Parameter to query with OpenRecordSet Command

R

rmcompute

Variable dtCurrent holds the curent date which is set up as a parameter in
query MainQueryWithParm.

Set rst = db.OpenRecordset("MainQueryWithParm", dtCurrent,dbOpenDynaset)

Is it not possible to pass a parameter in this way ?
 
O

Ofer

Try this instead
Remove the parameter from the query, and then open the recordset
Set rst = db.OpenRecordset("Select * From MainQueryWithParm Where FieldName
= #" & dtCurrent & "#")
 
R

rmcompute

Okay, I will try that, but are you saying there is no way to do it using the
query ?
 
R

rmcompute

Chris2,

I would like to execute the query from Excel and pass the parameter. Do you
know if it is possible to update the Parameter object from Excel after
creating an Access object ?
 
C

Chris2

rmcompute said:
Okay, I will try that, but are you saying there is no way to do it using the
query ?
Click to expand...

rmcompute,

In the file, DAO360.chm, look up:

"Parameter objects" and "Parameters collection".

Each QueryDef object has a Parameters collection. You must access
the Parameter objects to be able to pass parameters via DAO.


Sincerely,

Chris O.
 
C

Chris2

rmcompute said:
Chris2,

I would like to execute the query from Excel and pass the parameter. Do you
know if it is possible to update the Parameter object from Excel after
creating an Access object ?
Click to expand...

rmcompute,

You'll need a reference set to the "Microsoft DAO 3.6 Object
Library" (and possibly to the "Microsoft Access X.0 Object Library",
as well), but yes, you can. Just write your DAO code directly in an
Excel VBA Project.

You can create or reference TableDefs, QueryDefs, or whatever DAO
will do. You can do this from Word, too, or even PowerPoint.


Sincerely,

Chris O.
 
R

rmcompute

Thank you.

Chris2 said:
rmcompute,

You'll need a reference set to the "Microsoft DAO 3.6 Object
Library" (and possibly to the "Microsoft Access X.0 Object Library",
as well), but yes, you can. Just write your DAO code directly in an
Excel VBA Project.

You can create or reference TableDefs, QueryDefs, or whatever DAO
will do. You can do this from Word, too, or even PowerPoint.


Sincerely,

Chris O.
Click to expand...
 
Top