Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recorsource (A2000 SR1)

    I have a form with a subform.
    On the main form I will be adding 4 buttons which will change the recordsource of the sub.

    I can call a query,ie:
    Forms!labourbookingallrecords![LABOURBOOKINGALLsubform].Form.RecordSource = "labourbookingQ1"
    or with out a query write an SQL into the coding.

    I'm trying to write my db not saving too many queries to save on clutter.
    Which is the most efficient way ? for long term use ?

    Regards Dave

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Recorsource (A2000 SR1)

    It is my understanding that named queries are optimized during a compaction process, but I don't know if SQL code in a module is ever optimized. I personally find it easier to see what is going on in a query by opening the named query in the Access QBE grid, rather than just looking at the SQL code; but some SQL purists might disagree. On the other hand, it is easy to delete a named query, especially if you don't immediately recognize where it is used; a good name convention is vital here. All of which means, there probably isn't one right answer; so do what you are most comfortable with!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: RecordSource - Saved Query or SQL (A2000 SR1)

    If the queries are complex then using saved queries would be a good idea as the Jet database engine optimizes and "compiles" saved queries. As far as I know, there is no way to "optimize" an SQL statement saved in code module or entered as a form's (or subform's) RecordSource property or combo or listbox RowSource property. To compile and optimize a query, save in Design view then run query without saving it again. If you make changes to query, repeat this process. To be sure query is re-optimized, open in SQL view, make change, save, and run without saving again. For a good detailed discussion of how Jet optimizes queries, refer to Chapter 15 of the Access 2000 Developer's Handbook.

    Another issue to consider is whether your database is using Access user-level security. If so, saved queries may be preferable to SQL statements because there is no way to set Run With Owner's Permission (RWOP) property for an SQL statement. Some of my Access programs are in fact cluttered with 100's of saved queries because I'd rather use RWOP queries than grant users permission to update tables directly. I dislike database "clutter" but this was a trade-off made for security purposes. A good naming convention and useful query descriptions are necessary to keep the clutter in some semblance of order.

    If security concerns are not an issue then I'd recommend using SQL statements in place of saved queries where appropriate, especially for combo boxes, listboxes, etc, if the SQL statement is not complex and executes quickly. For complex SQL statements I prefer saved queries.

    HTH

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: RecordSource - Saved Query or SQL (A2000 SR1)

    The SQL stored in the rowsource of a combobox or recordsource property of a form or report is optimized on the fly the first time it runs in that session. It stays optimized until Access is closed then has to be reoptimized the next time you open the database.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RecordSource - Saved Query or SQL (A2000 SR1)

    Thanks for all your wise views on this subject.
    I now have a clearer understanding on which way forward to pursue.

    Regards
    Dave

Posting Permissions

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