How to use parameterized query with DAO Recordset?

[deko]

I'm trying to open a Recordset based on a parameterized query. I'm kind of
new to parameterized queries, so I'm sure I'm missing something simple.

Set qdfs = db.QueryDefs
Set qdf = qdfs("qryInvoices")
qdf.Parameters("prmInv") = strInvoice
qdf.Parameters("prmCid") = lngCustomerID
Set rst = db.OpenRecordset("qryInvoices")

[qryInvoices]
SELECT Customer_ID
FROM tblCustomers
WHERE (HyperlinkPart([Invoice], 0) = prmInv) And ([Customer_ID] =
prmCid);

Why doesn't this work? How do I open a Recordset based on a parameterized
query?

Thank in advance.

[lylefair]

Perhaps
Set rst=qdf.OpenRecordset([Type], [Options], [LockEdit])
?

[Wayne Morgan]

You set the parameters for qdf, but then you opened a second copy of the
query when you went back to using db in the statement db.OpenRecordset.
Instead, use qdf.OpenRecordset. You won't need to specify the query name,
because qdf is already a query.

Set rst = qdf.OpenRecordset

--
Wayne Morgan
MS Access MVP
"deko" <de**@nospam.com> wrote in message
news:8Z********************@comcast.com...

I'm trying to open a Recordset based on a parameterized query. I'm kind
of new to parameterized queries, so I'm sure I'm missing something simple.

Set qdfs = db.QueryDefs
Set qdf = qdfs("qryInvoices")
qdf.Parameters("prmInv") = strInvoice
qdf.Parameters("prmCid") = lngCustomerID
Set rst = db.OpenRecordset("qryInvoices")

[qryInvoices]
SELECT Customer_ID
FROM tblCustomers
WHERE (HyperlinkPart([Invoice], 0) = prmInv) And ([Customer_ID] =
prmCid);

Why doesn't this work? How do I open a Recordset based on a parameterized
query?

Thank in advance.

[deko]

> Set rst = qdf.OpenRecordset

Thanks - that was easy. I also discovered I had the constant wrong in the
WHERE statement - shd be 2 not 0

Set qdfs = db.QueryDefs
Set qdf = qdfs("qryInvoices")
qdf.Parameters("prmInv") = strInvoice
qdf.Parameters("prmCid") = lngCustomerID
Set rst = qdf.OpenRecordset

[qryInvoices]
SELECT Customer_ID
FROM tblCustomers
WHERE (HyperlinkPart([Invoice], 2) = prmInv) And ([Customer_ID] = prmCid);

[David W. Fenton]

"deko" <de**@nospam.com> wrote in
news:8Z********************@comcast.com:

I'm trying to open a Recordset based on a parameterized query.
I'm kind of new to parameterized queries, so I'm sure I'm missing
something simple.

Set qdfs = db.QueryDefs
Set qdf = qdfs("qryInvoices")
qdf.Parameters("prmInv") = strInvoice
qdf.Parameters("prmCid") = lngCustomerID
Set rst = db.OpenRecordset("qryInvoices")

[qryInvoices]
SELECT Customer_ID
FROM tblCustomers
WHERE (HyperlinkPart([Invoice], 0) = prmInv) And
([Customer_ID] =
prmCid);

Why doesn't this work? How do I open a Recordset based on a
parameterized query?

Lyle has asked your question, but I don't quite understand why you
have two paremeters. Can't an invoice be billed to only one
customer?

I just don't use parameterized queries, except for quick-and-dirty
forms and reports. I *never* use them in DAO, because it's messy to
assign the parameters, when it's easier to just write a WHERE clause
directly, with dynamic SQL, and open a recordset on that.

If you're working with a server back end, perhaps the parameterized
version will be more efficient, but you didn't say that was the
case.

I simply question the utility of using a saved QueryDef in this
context in the first place.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[deko]

> I just don't use parameterized queries, except for quick-and-dirty

forms and reports. I *never* use them in DAO, because it's messy to
assign the parameters, when it's easier to just write a WHERE clause
directly, with dynamic SQL, and open a recordset on that.

I think what you mean is this:

qdf.Sql = strSql

which is what I do most of the time. But if the parameter is the only thing
that changes or if the string contains quotes or special characters I like
using parameterized queries. As you know, one man's mess is another man's
elegance.

[David W. Fenton]

"deko" <de**@nospam.com> wrote in
news:TL********************@comcast.com:

I just don't use parameterized queries, except for
quick-and-dirty forms and reports. I *never* use them in DAO,
because it's messy to assign the parameters, when it's easier to
just write a WHERE clause directly, with dynamic SQL, and open a
recordset on that.
I think what you mean is this:

qdf.Sql = strSql

No, I don't mean anything of the sort.

I don't edit stored queries in code, except in very special cases
(such as wanting to base a Microsoft graph on a TOP N query).
which is what I do most of the time. But if the parameter is the
only thing that changes or if the string contains quotes or
special characters I like using parameterized queries. As you
know, one man's mess is another man's elegance.

I don't see the point of using them. I prefer to write the SQL
deynamically and execute it or open a recordset on it, rather than
having to deal with setting parameters, some of which may not always
be necessary in a particular context.

I do use a few parameter queries, but those are queries that are
never used in code.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[deko]

> I don't see the point of using them. I prefer to write the SQL

deynamically and execute it or open a recordset on it, rather than
having to deal with setting parameters, some of which may not always
be necessary in a particular context.

So do you mean:

db.Execute strSql

?

But isn't a stored query faster than this? From what I've read, the
advantage of a stored query is that it's already compiled and JET can
optimize it.

[David W. Fenton]

"deko" <de**@nospam.com> wrote in
news:HY********************@comcast.com:

I don't see the point of using them. I prefer to write the SQL
deynamically and execute it or open a recordset on it, rather
than having to deal with setting parameters, some of which may
not always be necessary in a particular context.
So do you mean:

db.Execute strSql

?

But isn't a stored query faster than this? . . .

Only under the most unusual circumstances, especially in the case of
action queries (the only kind that you can execute).
. . . From what I've read,
the advantage of a stored query is that it's already compiled and
JET can optimize it.

That only matters for complex queries that benefit from
optimization. I've never seen any issues with dynamic SQL being
slow.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc