On fly (VBA) Form's record source

[Babycat]

Hi everyone.

I have seen alot DB design with table, query, form and report... The table and form are essential i think. However, do we really need to make the fixed queries (save it with a name in navigation panel)?
Instead of making a query, I usually use SQL string and and assign it to form's recordsource with VBA, for example:

        SQLsub = User_SQLTableString_SubTransDetail _
               & "WHERE TBLPRODUCT.ProductID like '" & Cbx_F_MaVT & "' " _
               & " ORDER BY TBLTRANSACTION.CreatedDate DESC ;"
    
        Forms!FrmTransDetail.Form!SubFrmTrans.Form.RecordSource = SQLsub

In this way i have maximum flexibility to change the record source on the fly (when program is running).

But I am not sure if any advantage and disadvantage when doing this. Speed, CPU resource?

Below is an example of fixed query, Then form's record source is set to query Q_ProductID in form design

Please help to explain in detail if any difference btw these two ways.

Regards,

 

[isladogs]

These days, in my opinion, its largely personal preference.
I usually use SQL statements as I prefer all my code to be together & visible at a glance

In theory, using saved queries should be quicker than executing SQL as Access will create a query execution plan when it is first run in order to optimise the process. However, it is unlikely that you will notice much difference in practice.
Using a query def is slower than saved queries or SQL

I did a series of speed comparison tests on this exact topic a couple of years ago which you may find interesting.
Speed Comparison Tests 6 - Mendip Data Systems

 

[Babycat]

These days, in my opinion, its largely personal preference.
I usually use SQL statements as I prefer all my code to be together & visible at a glance

In theory, using saved queries should be quicker than executing SQL as Access will create a query execution plan when it is first run in order to optimise the process. However, it is unlikely that you will notice much difference in practice.
Using a query def is slower than saved queries or SQL

I did a series of speed comparison tests on this exact topic a couple of years ago which you may find interesting.
Speed Comparison Tests 6 - Mendip Data Systems

Thank you. In my case, the DB is pretty small and compact, I will continue with Query Def...
Thank again for your work of comparison, it is useful.

Regards.

 

[isladogs]

You're welcome.

 

[The_Doc_Man]

Another aspect of this is complexity. Using a QueryDef is like "not re-inventing the wheel." If you have a query that comes up frequently and doesn't change, why wouldn't you store it.

For example, suppose that you have a transaction-based topic (banking and inventory are the first two possibilities that come to mind), you get the balance of something (bank account or inventory item) by summing transactions using either or both of the GROUP BY and ORDER BY clauses with a SUM aggregate function and (possibly) a date as a parameter for a WHERE clause. Once you get this query built and get accustomed to using it, why would you ever want to go to the trouble of rebuilding it each time? Just supply the parameter and invoke it.

There is such a thing as query layering, where you build a query based on another query rather than a table. In particular if you have to analyze something involving UNION queries, it is not impossible to imagine basing your analysis query on a layered UNION query. Again, if the QueryDef of the underlying query is stable, why would you want to rebuild it?

There is also the issue with Domain Aggregate functions, where you can write that function on a table but you can also write it based on a query. In that case, the query MUST exist because you can't use DLookup or DMax on an SQL statement.

The point is, that QueryDef is a tool in your tool box. Just because you don't use it often, that doesn't mean you should discard it.

 

[Babycat]

There is also the issue with Domain Aggregate functions, where you can write that function on a table but you can also write it based on a query. In that case, the query MUST exist because you can't use DLookup or DMax on an SQL statement.

Yes, absolutely right. I have encountered with this problem. Can't use Domain Aggregate functions (Dcount, Dsum...) with SQL statement.
Thank for your detail clarification.

Regards.

 

[Pat Hartman]

However, do we really need to make the fixed queries (save it with a name in navigation panel)?

Only if you like efficiency. The first time they are executed, saved querydefs (including SQL strings in RecordSource and RowSource properties) Access calculates an execution plan and saves it eliminating the need to recalculate the plan every time the query runs.

Calculating execution plans used to be much worse in earlier versions of Access because in addition to the time it takes (not much in the greater scheme of things unless you run the same query dozens of times each time you open the app) you also had to contend with database bloat because Access didn't clean up after itself and so you could dramatically increase the size of a database in a single day if you had a lot of embedded queries and that caused a need to compact the FE frequently. The db bloat has been fixed so it is only the calculation time you have to worry about and it would be unlikely that you run queries enough times to make the time waste noticeable. I grew up writing CICS transactions on the mainframe with thousands of concurrent users and every wasted fraction of a second was costly so I am very sensitive to unnecessary calculations. Old habits die hard but if you know that one method is more efficient than another, why not just go with efficiency? Since the wastage affects mostly the user's personal PC rather than the entire network, I wouldn't worry about it. But you are making more of a maintenance problem for yourself than you would have if you used properly constructed and organized querydefs.

Most people don't want to change the recordsource on the fly. They just want to provide different values for the criteria - not the same thing.

Saved querydefs are much easier to keep current than embedded SQL spread hither, thither, and yon. For example, I have several calculated fields that I want every time I use certain tables. As Doc suggested, i create "base" queries that include these and all other queries select from these base queries rather than from the table directly. If I want to change the calculation, I have ONE place to change rather than potentially dozens..