Unable to pass Parameter to query with OpenRecordSet Command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rmcompute - 17 Nov 2005 23:12 GMT

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 ?

Reply to this Message

Ofer - 17 Nov 2005 23:20 GMT

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

Signature

I hope that helped
Good Luck

> 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 ?

Reply to this Message

rmcompute - 17 Nov 2005 23:30 GMT

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

> Try this instead
> Remove the parameter from the query, and then open the recordset
[quoted text clipped - 7 lines]
> >
> > Is it not possible to pass a parameter in this way ?

Reply to this Message

Chris2 - 18 Nov 2005 01:37 GMT

> Okay, I will try that, but are you saying there is no way to do it using the
> query ?
[quoted text clipped - 10 lines]
> > >
> > > Is it not possible to pass a parameter in this way ?

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.

Reply to this Message

rmcompute - 18 Nov 2005 02:11 GMT

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 ?

> > Okay, I will try that, but are you saying there is no way to do it
> using the
[quoted text clipped - 27 lines]
>
> Chris O.

Reply to this Message

Chris2 - 18 Nov 2005 05:50 GMT

> > > Okay, I will try that, but are you saying there is no way to do it
> > using the
[quoted text clipped - 33 lines]
> know if it is possible to update the Parameter object from Excel after
> creating an Access object ?

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.

Reply to this Message

rmcompute - 19 Nov 2005 13:46 GMT

Thank you.

> > > "rmcompute" <rmcompute@discussions.microsoft.com> wrote in
> message
[quoted text clipped - 56 lines]
>
> Chris O.

Reply to this Message