Solved Insert Query vs VBA Code

[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

 

[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.

 

[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/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

www.isladogs.co.uk

I've not seen Gina's version

 

[Thales750]

This is an enhanced version of Allen's code.

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

www.isladogs.co.uk

I've not seen Gina's version

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

 

[Thales750]

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.

I'm sorry to tell you, that is completely not true on so many levels.
First, if you use Saved Queries for deletes, you will create bloat. SQL Statements do not cause bloat.
Second, As I have said before, saving thousands of queries only serves to create naming chaos.
Saved Queries should be limited to queries with little to no conditions or criteria.
Saved Queries best use is as the query inside of SQL Statement.
SQL Statements are clean, and you can find them right there in module connected to the form or report you are working on.

Admittedly ultra complex Queries don't always work in SQL Statements and may need to be made up of multiple Saved Queries.

Who listed this thread as solved? it seems it has a ways to go.

 

[Thales750]

Deleting records with a query def in Access can cause database bloat because the old object is not deleted, but marked as such until you do a compact 1. This is because Access does not have true row locking, but has what is called database page locking. So, if you turn on row locking, then Access just expands all records to the size of one database page, resulting in massive bloating 2.

On the other hand, running a SQL statement in the module does not create bloat because changing the SQL property doesn't create a new object, just changes it 1.

I hope this helps!

BingGPT found this on the Stack Overflow Site.