Solved Insert Query vs VBA Code (1 Viewer)

[MattBaldry]

Hi all,

I have always used a method of creating an insert query and then calling this with VBA when I need to add data to a table.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryInsertReturnAction", acViewNormal, acEdit
DoCmd.SetWarnings True

Is there any benefit to changing this to have the SQL code in the VBA instead of calling the query?

~Matt

 

[ebs17]

If the SQL statement is constant, you get no benefit. On the contrary: The OpenQuery method cannot do anything with an SQL statement, so you will run into an error.

The second point: For a query saved as an object, the first time the query is executed, an execution plan is determined and saved that contains the result of optimizing the query flow. Each subsequent execution of the query uses this execution plan directly, eliminating the time it takes to create it. So the query will run faster.
However, this speed advantage is usually only in the very low millisecond range, which is not noticeable and usually not even measurable.

Better call

CurrentDb.Execute "qryInsertReturnAction", dbFailOnError

If query execution errors actually occur, you should know about them so that you can respond appropriately.

 

[MattBaldry]

If the SQL statement is constant, you get no benefit. On the contrary: The OpenQuery method cannot do anything with an SQL statement, so you will run into an error.

The second point: For a query saved as an object, the first time the query is executed, an execution plan is determined and saved that contains the result of optimizing the query flow. Each subsequent execution of the query uses this execution plan directly, eliminating the time it takes to create it. So the query will run faster.
However, this speed advantage is usually only in the very low millisecond range, which is not noticeable and usually not even measurable.

Better call

CurrentDb.Execute "qryInsertReturnAction", dbFailOnError

If query execution errors actually occur, you should know about them so that you can respond appropriately.

Thank you for your feedback. I think I am going to move to the VBA method. I want to reduce the number of objects in my DBs and this will be a decent start.

~Matt

 

[isladogs]

I agree with all the points made by @ebs17.
Many years ago, I changed from using mainly saved queries to largely using sql statements in VBA but that's from personal preference rather than because its more efficient for Access.
Be aware that you can only execute action queries but it is possible to run e.g. SELECT statements in VBA using query defs.

Some time ago, I wrote an article comparing the speeds of the different methods:

Speed Comparison Tests 6

This article compares the use of saved queries, query SQL and query definitions. It is the sixth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

Spoiler alert: The time differences are usually negligible and not worth worrying about

 

[MattBaldry]

I agree with all the points made by @ebs17.
Many years ago, I changed from using mainly saved queries to largely using sql statements in VBA but that's from personal preference rather than because its more efficient for Access.
Be aware that you can only execute action queries but it is possible to run e.g. SELECT statements in VBA using query defs.

Some time ago, I wrote an article comparing the speeds of the different methods:

Speed Comparison Tests 6

This article compares the use of saved queries, query SQL and query definitions. It is the sixth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

Spoiler alert: The time differences are usually negligible and not worth worrying about

Thank you for your comments. This one for me is more about my own preference than down to speed.

I wanted to learn the VBA side of it instead of me taking the "easy way" and creating the query and calling it.

I have changed to the CurrentDb.Execute strSQL rather than DoCmd.RunSQL strSQL though after reading your post.

This would only be for my action queries, my select ones will still be created objects.

Over the many years I have grown my DB I have changed my workings and I am now trying to standardize everything I have done as to stop confusing myself as to where things are.

My next post will be about Modules to see if there is a better way for me.

~Matt

 

[isladogs]

Make sure you add dbFailOnError whenever you do a DB.Execute statement

 

[CJ_London]

Many of my forms, combo’s and listboxes are quite dynamic so I use vba to either populate the recordsource/rowsource assign the record set directly

me.recordsource =sqlstr
Or
Set me.recordset = db.openrecordset(sqlstr)

 

[MattBaldry]

Many of my forms, combo’s and listboxes are quite dynamic so I use vba to either populate the recordsource/rowsource assign the record set directly

me.recordsource =sqlstr
Or
Set me.recordset = db.openrecordset(sqlstr)

Hi CJ,

I normally have my listboxes/combo boxes row source as a pre-made query. Would your method be a better option for me to learn?

~Matt

 

[MajP]

Would your method be a better option for me to learn?

I would not say "better", but I would say learn it in "addition".
There are times like Cascading Comboboxes that you want to modify the rowsource of the second combobox depending on selection of the first combo. Or maybe different users get different selections based on permissions.

 

[ebs17]

You can also enter a (constant) SQL statement directly into the RowSource property of a ListBox and the RecordSource property of a form instead of the name of a saved query. According to the literature (Michael Zimmermann), an execution plan is also created and used for this.

Advantage: The queries are integrated directly into the definition of the form; if you copy the form, everything is included.

Disadvantage: If you wanted to analyze all the queries yourself, the different placements of the queries would require additional effort (which not everyone can handle).

 

[CJ_London]

another advantage, queries are visible in the navigation window so someone can still mess with it, unintentially or otherwise. Yes you can hide it and/or hide the navigation window and take other measures but someone in the know can still find it. If you provide users with a .accde (with sql embedded in VBA and a separate back end) then they cannot change anything, tho' someone really in the know can probably still work out the sql code.

Most queries are 'one off' i.e. only used in one place. Some used in multiple locations are typically very simple (such as those used for lookups).

So from my perspective, as a security procedure, worth doing. But it is a personal choice.

According to the literature (Michael Zimmermann), an execution plan is also created and used for this.

yes - they are the ones that start with ~sq_ but not visible in the navigation window

 

[Pat Hartman]

Thank you for your feedback. I think I am going to move to the VBA method. I want to reduce the number of objects in my DBs and this will be a decent start.

I guess you didn't listen to the answer you got from ebs. There is no advantage to using VBA but there is an advantage to using querydefs.

At this point in the development of Access and the state of standard PC hardware, speed isn't going to be a factor unless you are running the SQL hundreds of times forcing Access to rebuild the same execution plan hundreds of times. There used to be a huge benefit to using querydefs due to the elimination of bloat. Creating an execution plan takes a lot of work space that that creates bloat but MS has figured out to minimize that so it is no longer the problem it once was.

So, that leaves it to personal choice. I have to tell you I am biased. I've been writing SQL for something in the neighborhood of 50 years and for the first 25 it was entirely by hand. Hard coded, embedded SQL. I used to dream of a tool like QBE although I would like it to be better than it is after 30 years. Let me list the problems I have with embedded SQL.

1. embedded SQL is not reusable unless you isolate it in submodules which no one ever does - querydefs are reusable.
2. If you change embedded SQL, it is changed in ONE place. If you want to change all copies, first you have to find them. Changing a shared querydef doesn't leave dangling unchanged queries. I sometimes create base queries that include certain types of calculated fields such as FullName which is a concatenation of first, middle, and last names and possibly the prefix and suffix also depending on the application. This makes it easy to just join this "base" query to a more custom query. Access figures it out when it creates the execution plan and optimizes the query correctly.
3. You have to run the code to test the query. With the QBE, you can run the queries outside of the code, especially if they get their arguments from form controls.
4. You have to either cut and paste a lot or have all the table names and column names locked in your mind so you can type them without mistakes. Personally, as a consultant, I work on too many applications at any given time to keep all the names straight, especially since I may not have built the tables myself. To me the QBE is a life saver. It is point and click.
5. An execution plan has to be created each time the query runs rather than once, the first time the query runs.

That gets us to the bad parts.

1. the QBE has this dreadful habit of rewriting your beautifully formatted SQL into unreadable strings with enough pairs of parens to choke a horse. This doesn't bother me. Why? I never look at the SQL. If I don't look at it, I won't be unhappy. In the cases where I do want to preserve the SQL strings, there is a trick. You just need to exercise some caution. Make sure you save the querydef from SQL view and refrain from changing to QBE view or if you do, be careful to cancel any request to save your changes otherwise, Access will rewrite your code and make you cry. I keep a table to hold certain SQL strings for just this purpose. Sometimes I have really complicated wHERE clauses with ANDs and ORs I carefully assign the parentheses so that the statement is evaluated as I intend it to be but Access just duplicates everything, making the query totally unreadable. Therefore, when I save changes to the querydef, I save the SQL string in my backup table so If I lose my mind and save in QBE view, at least I can get back the string as it was before Access broke it.
2. the QBE is as old as Access so it doesn't have any of the good features a modern version would have like comments. MS keeps promising a replacement but has broken that promise a dozen times so don't hold your breath.
3. The QBE has no good way to display subqueries

 

[ebs17]

The QBE has no good way to display subqueries

SQL Editor Add-In Recommendation

Do you use any third party Add-in or application for replacing/enhancing the SQL editor? This has been a long time complaint. Doing just a Web search I see there is quite a bit out there. Anyone have experience or recommendations? Anyone built one? Could be a good group project. Also a year...

www.access-programmers.co.uk

The SQL statement that you see in the SQL view contains the entire definition of the query. Ultimately, the SQL statement is also executed. It is therefore worth taking a look at your own SQL. If you format the view, as is usual with VBA code, you can also purchase the content if it is a little more and a little more complex.

 

[isladogs]

@ebs17
The meaning of the end of your final sentence has got lost in translation. By that I mean the section starting 'you can also purchase...'
Did you mean that a complex subquery can be separated out into its own query if needed to help understand its purpose?

 

[ebs17]

First, what I mean is that I can read a query as is. I can read and write the following query (composing consecutive days into time periods) throughout the entire piece. As is well known, I have fewer reservations about using subqueries than many others. I have ideas about how far I can expand this before I run into problems.

SELECT
   T.Name,
   T.Datum AS Beginn,
   (
      SELECT
         MIN(U.Datum)
      FROM
         (
            SELECT
               T1.Name,
               T1.Datum
            FROM
               34_tblDatumswerte AS T1
            WHERE
               NOT EXISTS
                  (
                     SELECT
                        NULL
                     FROM
                        34_tblDatumswerte AS X
                     WHERE
                        X.Name = T1.Name
                           AND
                        X.Datum = T1.Datum + 1
                  )
               ) AS U
      WHERE
         U.Name = T.Name
            AND
         U.Datum >= T.Datum
   ) AS Ende
FROM
   34_tblDatumswerte AS T
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            34_tblDatumswerte AS X
         WHERE
            X.Name = T.Name
               AND
            X.Datum = T.Datum - 1
      )

Advantage (for me): I see the entire processing from table to result. I see all tables involved.
Having an overview can be very helpful in identifying unnecessary things (fields that are not used, sorting at deeper query levels).

For more complex queries from others, I may break them down into sub-queries for the process of understanding.

Without formatting, the query shown would be a disaster and incomprehensible.
According to the flow of query processing, I start with the FROM part when reading. In this query FROM already exists five times. Without orientation you are lost.
In the QBE you would see practically nothing.

 

[Pat Hartman]

@ebs17 You work the way you prefer and that's fine but the simplistic examples you post don't reflect the reality of more complex queries.

 

[ebs17]

but the simplistic examples you post don't reflect the reality of more complex queries

Of course I reached for a lower level of difficulty, so it should still be understandable for a wider circle.
However, some people will describe what is shown as complex and cannot simply recreate it from their own heads.

Complex queries - what does that mean?

Long queries (lots of characters) are not necessarily complex, just long. My longest query had around 1,300 characters, but there was actually just multiple repetitions of simple logic (put together using VBA).

Complex logic? For me that wouldn't be a goal, but would at best result from the task at hand.
It's a sign of genius to make difficult things simple.

 

[Thales750]

I work on very large multiyear multi developer projects, that's my slant.

Tables by the hundreds. Forms, both current and those needing to be depreciated. Tables not used in years.

And, so many thousands of similarly named queries, the river has long since flowed over the banks.

We found that we make basic saved Queries (QueryDefs) with either little, or no, criteria. These queries are used over and over and they act as table in many ways. That way all filtering, all functions, all lookups, whatever, are specific to the current procedure.

This method is way easier to organize.

Before he retired, Allen Browne created a converter that takes querydef language and turns it into VBA compatible code.

Gina Whipp modified it to work with Action Queries, and I modified that to work better with SQL Server. I'll talk to Gina to see if she wants to share it

 

[isladogs]

This is an enhanced version of Allen's code.

SQL to VBA & Back Again

This is an enhanced version of a utility, by Allen Browne which makes it easy to convert a query from SQL to VBA (or vice versa)

www.isladogs.co.uk

I've not seen Gina's version

 

[Thales750]

This is an enhanced version of Allen's code.

SQL to VBA & Back Again

This is an enhanced version of a utility, by Allen Browne which makes it easy to convert a query from SQL to VBA (or vice versa)

www.isladogs.co.uk

I've not seen Gina's version

Her version executes action queries.
I'll call her at lunch