DAO Querydefs

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


.

Relevant Pages

  • Re: Dealing with large recordsets
    ... "MyRecID" is the field that is numbered to be able to select groups of records instead of all the records. ... Dim i As Integer, k As Long ... Set rst = db.OpenRecordset ... I run a series of queries on this table to create what is essentially a pivot table in Access 2000. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simpler alternative to replication
    ... queries to create a new table containing all the new data. ... details of report.I have a form for creating a new report. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: Forcing a ReQuery
    ... queries 1 and 2, which you say works correctly, or in the fact that queries ... > Dim dbs As DAO.Database ... > Set rst = qdf.OpenRecordset ... > Set qdf = Nothing ...
    (microsoft.public.access.modulesdaovba)
  • Re: Code to list Objects in DB lists deleted SQL
    ... Tom (that they're temporary queries). ... lists the queries it lists some which look like they are the statements ... Dim MyQueries() As String ...
    (microsoft.public.access.formscoding)
  • Re: Export Excel To Access Wizard HELP!!!
    ... Depending on what your Excel code is doing and what's in your queries, ... The sample procedure below opens a secured mdb and will work with minor ... Dim dbW 'As DAO.Workspace 'to change from late to ...
    (microsoft.public.access.externaldata)