Results 1 to 5 of 5
  1. #1
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18

    Efficiency/clutter question: Using queries or VBA functions for tasks..

    Hi all, hope your day is going well.



    I've spent some time now developing the functionality for a database my company will be using going forward. I want to optimized it and de-clutter it as much as possible and I was wondering if I should focus on optimizing query design or shifting from queries into VBA functions. As it stands right now I have ~40 queries, many of which are just used to find something simple as a criteria for another query. Let me give you a few examples to explain...

    I have a query, 'qryFindMostRecentReportingDate', that's entire purpose is to find the most recently submitted report for each different portfolio we manage. To take it one step further, I have another query 'qryFindMaxMostRecentReportingDate' to find the portfolio that has submitted a report most recently. I can simplify this process by writing code like the following:

    Code:
    Public Function getMostRecentReportedDate(Optional mandateID_Key As Variant = Null)
        If IsNull(mandateID_Key) Then
            getMostRecentReportedDate = DMax("[reportingDate]", "tblMandateReports")
        Else: getMostRecentReportedDate = DMax("[reportingDate]", "tblMandateReports", "[mandateID_FK] = " & mandateID_Key)
        End If
    End Function
    My question, then, is it worth it to convert as many of my queries into VBA functions as possible, which can just be called as needed?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    So you have a query to return a single value then join that query to another table/query?

    Don't think I've ever done that.

    My personal preference would be to eliminate the query objects.

    I also do not use dynamic parameterized queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18
    Quote Originally Posted by June7 View Post
    So you have a query to return a single value then join that query to another table/query?

    Don't think I've ever done that.

    My personal preference would be to eliminate the query objects.

    I also do not use dynamic parameterized queries.
    Basically - yes. The query that returns most recent reporting dates for a portfolio is only used as a on-the-fly criteria for report generation (i.e., only show the current portfolio value where date matches most recent reporting date)

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ChaseC View Post
    The query that returns most recent reporting dates for a portfolio is only used as a on-the-fly criteria for report generation (i.e., only show the current portfolio value where date matches most recent reporting date)
    For something like this I'd think that you'd be much better off simply using a function, such as DMax() against the dates, than running a Query.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Location
    Prince George, Virginia
    Posts
    16
    I agree with Missinglinq's assesmemt regarding this particular query...that being said, i prefer to use stored queries instead of functions. Especially action queries.

    For one thing, my SQL skills are not that good. Why do what Access already does, and does it better?

    Also, stored queries are more efficient and run faster because Access has already filed a "plan" after the first time it executes.

    I do understand your desire to clean up the navigation pane, but there are ways to do that. Also, I personally MEVER allow my users to see the navigation pane. Lord know they mess things up enough without them peeking "under the hood"!

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

Similar Threads

  1. Logical Functions in queries
    By Mschneider331 in forum Access
    Replies: 3
    Last Post: 10-31-2016, 06:14 PM
  2. Logical Functions in Queries
    By Mschneider331 in forum Access
    Replies: 5
    Last Post: 10-31-2016, 09:59 AM
  3. Replies: 3
    Last Post: 04-26-2016, 10:40 AM
  4. functions / queries
    By merlin2049er in forum Queries
    Replies: 7
    Last Post: 06-10-2015, 09:29 AM
  5. User functions within queries...
    By ChaosInACT in forum Queries
    Replies: 5
    Last Post: 01-19-2012, 06:39 PM

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