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:
- 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...
- 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.
- 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?