Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: Converting regular queries into pass-throughs

    Hey there! I have got an access query which I would like to convert into a pass through. The only problem is that the current query has stuff in the where clause which comes from a form eg:

    where user_login = [Forms]![frm_Application]![user_login]

    These queries are ultimately the data source for a report, so I can't just create DAO objects and populate the arguments that way, I'd like to keep this as being Access "Query" objects available from the "Queries" tab.

    Is there any way to do this with a pass through, or am I stuck getting rid of the where clause and then building a regular Access query object off of the pass through? Will I lose all the nice performance advantages of using a pass through by doing it that way? Thanks!

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Yes you can.

    But you will need to use some string manipulation to change the querydef object's SQL property.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jan 2004
    Posts
    32
    Do tell! Oh, I'm using Access 2000 btw.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Well, suppose you have a pass through query, which is saved in the Access query tab as qpt_GetEmployees, then . . .

    . . . whenever you want to execute that query, immediately before executing it, or, immediately before opening the form that that query might be based upon, you would need to do the following, in code:

    Dim qdef as querydef

    set qdef=CurrentDB().QueryDefs("qpt_GetEmployees")

    qdef.SQL="SELECT * FROM tblEmployees WHERE [user_login]='" & [Forms]![frm_Application]![user_login] & "'"

    set qdef=NOTHING

    That's it.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jan 2004
    Posts
    32
    That worked wonders for the base report, but I would like to do this also for the subreports, but access is telling me that a subreport cannot be based on a pass through query. Any way around this? Thanks!

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You can base the subreport on a temporary table which looks exactly like the output of the pass-through query. Then, on the OnOpen of the report, clear this temporary table and use a wrapper query (an append query which uses the pass-thorugh query as it's SELECT source) to append to that temporary table.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •