Access Queries vs SQL run by VBA

Question

Hi

What are the benefits of running a Query via SQL in VBA (if any)

---

Cheers // Peter Forss Stockholm

Answer 1

What are the benefits of running a Query via SQL in VBA (if any)

Hi Peter,

What do you mean: using a predefined query (QueyDef), versus the construction of a SQL-string in VBA?

Imb.

Answer 2

When you save a query, Access optimizes its execution the first time you run it, so it should be faster after that. Whether you'll notice this in real life depends on the complexity of the query and on the number of records in the table(s) involved, of course.

Executing a SQL statement in VBA doesn't have the advantage of optimization, since the string has to be evaluated each time. But it has the advantage that you can assemble the SQL string dynamically. For example, you can built the WHERE clause based on what the user has selected/entered in controls on a form. It can be much easier to build the required logic step by step in VBA code than trying to handle all possibilities in the criteria of a saved query.

So both saved queries and dynamically generated SQL have their place.

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 3

Agree with Hans. Each approach has its uses though in the end it comes down largely to personal preference for most situations. In the distant past I mainly used saved queries. These days I almost always construct SQL in the VBE

Just to add to Hans' comments, you may find it useful to read my article comparing these approaches Query vs SQL vs QueryDef. The article  considers the merits of each approac  and investigates the speed of each method for different types of query. Surprisingly, SQL is often faster than a saved query despite ACE creating an execution plan for saved queries. Using query refs is almost always the slowest method.

Answer 4

The query optimization from saved queries is pretty much no longer apparent because of current hardware.  Go back to the 80's and it was something to ponder, but now, not so much.

The one other aspect worth noting is the fact that using VBA allow you a certain level of security in the sense that saved queries are accessible and users can run amuck if not hidden properly.  So by dynamically creating your queries through VBA you can protect these objects from those dangerous users.  Just an extra layer of protection IMHO.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 5

What are the benefits of running a Query via SQL in VBA (if any)

Hi Peter,

I have always wondered what the optimalization of saved queries would be. I have not come further then a reconstruction of the sql-string for faster interpretation, but I can be wrong. How are fields with a key in the where-clause taken into account, eventually combined with filters?

In my applications I do not use any stored queries. All sql-strings are generated “on the spot”, depending on the current conditions.

I believe that best efficiency is gained with “strong” primary keys, that is as short as possible. In that light all tables have an autonumber field as primary key, included all join tables.

For any continuous overview I use one generalized form. This form then is tuned by a “Form_tbl” and a “Control_tbl”. In the Form_tbl I have a field that holds that part of a sql-string that describes all the joins. So locally I have only to deal with selected fields and the where-clause. The “join-part” of the sql-string is automatically updated after adding or deleting a alternate key field.

I generally use simple queries. I haven’t found a place where I needed a really complex query. They mostly seem to be used in Reports, but I have my own reporting system.

With this systematics I succeeded to make “distributed” databases, as I can display in any application a generalized form with “external meta data” working on “external data”.

So, sql-string generation in VBA gives tremendous possibilities.

Imb.

Answer 6

Whichever method you use, the speed will depend on how well optimised the query/sql is. The time differences can be significant - much greater than any difference between the different approaches.

I have another article related to this specific topic. See Optimise queries