Results 1 to 3 of 3
  1. #1
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197

    How to pass parameters "the best way"


    So here is a question about query parameters for queries, that base on linked sql server tables in access. I know of several ways to pass parameters in a query in general:
    1. Use a form and reference the form from the query. Works fine as long as you don't use the query with Database.OpenRecordset, what I do...
    2. Use parameters in the query and fill them with QueryDef.Parameters. Works nice from code, but leads to ugly parameter input boxes when executing the query as a recordsource for a form for ex.
    3. Use a VBA function that returns the needed value. Works nicely from code and in forms, but as I can't find a way to mark a method as deterministic, I think access isn't able to pass the filters based on such a function to sql server, as it reevaluates the function for every record.


    As you see, afaik every method I know has a drawback. In my case the value used for filtering here is something like a workarea selection that rapidly reduces the records, that are returned by mostly any query I have in my database. Selecting it is the users first step and after that it will not change as long as the user doesn't explicitly change it (so its not realy deterministic, but for the time a query runs it is). What I want to know is: did I miss an option here? or can I work around the drawbacks of one of the methods? or do I really have to have every query twice, once as option 1 and once as option 2?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I have been using something like the following for a dynamic parameterized query object.
    Code:
    WHERE (((tblPurchOrd.PurchOrdPK)=IIf(Eval('[Forms]![frmInventory]![cmbPO]')<>"",Eval('[Forms]![frmInventory]![cmbPO]'),tblPurchOrd.PurchOrdPK)) And
    Not sure of the relevance using SQL as the BE (performance).

    Paul helped to employ the Eval function to get the query object to work within DAO. Also, Orange linked to a function that will employ the Eval function for you. More on that here.
    https://www.accessforums.net/program...dao-42878.html

  3. #3
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Eval, as any other function from VBA that hasen't a SQL server pendant, will force access to client side filtering as the output is non deterministic. That leads to downloading all records from the backend and dismiss the once, that don't match the where clause. That is what I ran to when using option 3. But you are right, for being able to use form references in a query used with DAO Eval is a solution I didn't thought of.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 27
    Last Post: 12-17-2013, 08:44 AM
  2. Replies: 13
    Last Post: 01-30-2013, 03:05 PM
  3. Automating "Append" Query with Parameters
    By Monterey_Manzer in forum Queries
    Replies: 1
    Last Post: 10-04-2012, 12:00 PM
  4. UPDATE function "too few parameters"
    By eww in forum Programming
    Replies: 5
    Last Post: 05-11-2011, 09:38 AM
  5. Can I pass "sort by" using DoCmd.OpenReport
    By alsoto in forum Reports
    Replies: 3
    Last Post: 04-16-2009, 08:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums