By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

DAO Querydefs

P: n/a
Hi All,
what I am trying to achieve is rather than having loads of queries
stored within the database soley to use as a DAO recordset, I would like to
define the query within code. My problem is that a lot of these queries use
parameters and I do not know how to define these type of queries within
code.

My normal plan of attack would be to create and save the following SQL as a
query:

PARAMETERS lIORDOUID Long;
SELECT tbl_IORDOU.IORDOUID, tbl_IORDOU.IORDSTA, tbl_IORDOUL.Sku
FROM tbl_IORDOU INNER JOIN tbl_IORDOUL ON tbl_IORDOU.IORDOUID =
tbl_IORDOUL.IORDOUID
WHERE (((tbl_IORDOU.IORDOUID)=[lIORDOUID]));

I would then use the query as follows:

Set Qdef = CurrentDb.QueryDefs("Qry_NAME")
With Qdef
.Parameters(0) = SOME_PARAMETER
Set Rst = .OpenRecordset
End With

What I would like to do is something like:

Dim Qdef As DAO.QueryDef
Dim Rst As DAO.Recordset
Dim StrSQL As String

StrSQL = "PARAMETERS lIORDOUID Long; " & _
"SELECT tbl_IORDOU.IORDOUID, tbl_IORDOU.IORDSTA, tbl_IORDOUL.Sku " & _
"FROM tbl_IORDOU INNER JOIN tbl_IORDOUL ON tbl_IORDOU.IORDOUID =
tbl_IORDOUL.IORDOUID " & _
"WHERE (((tbl_IORDOU.IORDOUID)=[lIORDOUID]));"

Set Qdef = CurrentDb.QueryDefs(StrSQL)
With Qdef
.Parameters(0) = pIORDOUID
Set Rst = .OpenRecordset
End With
Could someone please tell me is this is possible and if so where I can find
some information?
Thanks in advance,

Mark
May 15 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Mark,

Something else you could try instead of setting a recordset object to a
querydef is to set the recordset object directly to the sql like this:
Dim DB As Dao.Database, RS As Dao.Recordset
Set DB = CurrentDB
Set Rst = DB.OpenRecordset("Select Count(*) From tblx Where fldx = '" &
txtName & "'")
If Not Rst.EOF Then txtCountOfNames = Rst(0)

Here you can add your param(s) directly inline with the Sql. Then check
if any data was retrieved by checking if Rst is at the end of file
(EOF). If Rst.EOF = true, then you did not get any data. If Not
Rst.EOF = True, then you did get some data. In my sample, you will only
get one row (and just one field) which would be a count of records where
the Name field in the table contains the name that would be entered in
the Names Textbox. If you don't check for EOF, you will get an error if
you try to read Rst and it does not contain any records. Also, Rst(0)
is the index of the first field in the recordset you just picked up in
this sample. I could have named the result of Count(*) like this;

("Select Count(*) cnt From tblx Where fldx = '" & txtName & "'"

Then I could say this: ...txtCount = Rst!cnt. In the first sample I
could not do this because I did not give the Count(*) field a name. So
I reference it by the field index.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
May 15 '07 #2

P: n/a
Hi Rich,
thanks very much for your suggestion. I think sometimes, you spend so
much figuring out how to do something a particular way, you don't see the
wood for the trees.

Excellent advice!!!

Thank you again.

Mark

"Rich P" <rp*****@aol.comwrote in message
news:46*********************@news.qwest.net...
Hi Mark,

Something else you could try instead of setting a recordset object to a
querydef is to set the recordset object directly to the sql like this:
Dim DB As Dao.Database, RS As Dao.Recordset
Set DB = CurrentDB
Set Rst = DB.OpenRecordset("Select Count(*) From tblx Where fldx = '" &
txtName & "'")
If Not Rst.EOF Then txtCountOfNames = Rst(0)

Here you can add your param(s) directly inline with the Sql. Then check
if any data was retrieved by checking if Rst is at the end of file
(EOF). If Rst.EOF = true, then you did not get any data. If Not
Rst.EOF = True, then you did get some data. In my sample, you will only
get one row (and just one field) which would be a count of records where
the Name field in the table contains the name that would be entered in
the Names Textbox. If you don't check for EOF, you will get an error if
you try to read Rst and it does not contain any records. Also, Rst(0)
is the index of the first field in the recordset you just picked up in
this sample. I could have named the result of Count(*) like this;

("Select Count(*) cnt From tblx Where fldx = '" & txtName & "'"

Then I could say this: ...txtCount = Rst!cnt. In the first sample I
could not do this because I did not give the Count(*) field a name. So
I reference it by the field index.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

May 16 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.