Solved Insert Query vs VBA Code

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

 

[Josef P.]

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.

vs. text from 1 (answer from Tom Collins):

Deleting and recreating an object will cause your DB to bloat because the old object isn't really deleted, just marked as such until you do a compact. Changing the SQL property doesn't create a new object, just changes it.

The first quote could possibly be misinterpreted.

Note: It doesn't matter how you delete a data record in an Access database (via a saved query or a dynamically generated SQL statement that is then executed), it is always first marked as "deleted" and only finally removed during cleanup (compact).

 

[isladogs]

As already mentioned in post #4, I wrote an article a long time ago comparing the efficiency of saved queries, query defs and sql statements

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

Leaving aside for now the issue of bloat, the results are 'nuanced' but one certainty is that query defs are NEVER the most efficient approach.
Having said that, with modern CPUs the time differences are usually negligible.

 

[Thales750]

As already mentioned in post #4, I wrote an article a long time ago comparing the efficiency of saved queries, query defs and sql statements

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

Leaving aside for now the issue of bloat, the results are 'nuanced' but one certainty is that query defs are NEVER the most efficient approach.
Having said that, with modern CPUs the time differences are usually negligible.

The thing is, all things being equal, SQL imbedded in the Module is cleaner. I can't really enter the debate about deleting records causing bloat one way or the other. I can only go by what others say. And Stack Overflow has been a reliable source since the beginning.

What I have noticed is zero bloat on compiled front ends and SQL Server backend for the last 10 years. Actually forever for that matter.

 

[Thales750]

Be aware that you can only execute action queries but it is possible to run e.g. SELECT statements in VBA using query defs.

I don't know if I understand your statement, I have countless Select Queries.

strSQL = "SELECT tblCustomerProfile.cpCustomerID, tblCustomerProfile.cpCompanyName, tblCustomerProfile.cpCustomerStatusID, tblCustomerProfile.cpArchive, tblCustomerProfile.cpTenantID " & vbCrLf & _
"FROM tblCustomerProfile " & vbCrLf & _
"ORDER BY tblCustomerProfile.cpCompanyName;"

 

[Thales750]

SQL to VBA this can be used to convert queries created as a querydef into SQL code for use in modules.
This was originally created By Allan Browne, Action Queries were added by Gina Whipp, and it gets the last Primary Key from SQL Servers by me.

 

[The_Doc_Man]

What I have noticed is zero bloat on compiled front ends and SQL Server backend for the last 10 years. Actually forever for that matter.

On any active SQL back-end processor, I would expect idle-time garbage collection to eliminate bloat - not to mention that many such active back-end systems use a "fill" ratio to leave extra space in data blocks so that if the records aren't exactly the same, it still doesn't matter that much. I know for a fact that ORACLE server-side processing included automatic rebalancing of the "data buckets" that they used. I won't swear to it for SQL Server but was led to believe that it also does some type of proactive cleanups.

For native Access back-end files, garbage collection is deferred until the Compact & Repair action. There, I would expect bloat. The distributed nature of file management for native Access back-end files makes proactive cleanup nearly impossible based on destructive interference being an issue. That's why C&R is recommended to be done when the DB is idle.

 

[Pat Hartman]

First, if you use Saved Queries for deletes, you will create bloat

What are you talking about? That reference from Stack Overflow makes no sense. The running of a saved querydef does not cause bloat. What causes bloat is the process of replacing records by delete/append queries. The make/table technique is the same problem. And it doesn't matter whether the query is embedded in the VBA or a querydef. Jet and ACE do not reuse the space freed up by deleting records. Therefore, if you have a process where you need to import temporary records, there are better techniques such as using a "side" BE to hold the temp data and replacing the "side" BE each time you have a new import to perform. Make tables are usually not necessary at all and should be replaced by select queries.

The thing is, all things being equal, SQL imbedded in the Module is cleaner.

That is your opinion and I disagree but we don't need to go there again. Regarding efficiency, there is little difference. In earlier versions of Access, querydefs were more efficient for two reasons. Today's Access versions have solved the bloat problem caused by generating an execution plan every time you have to run embedded SQL but the time to calculate the execution plan is not significant enough to worry about unless perhaps you are running a query inside a loop that will cause it to execute thousands of times. And even in this case, my understanding is that Access has gotten smarter. I think it generates a new execution plan only the first n times you run the query inside the loop but eventually, just reuses the same plan. I don't know if running multiple different queries inside the loop has any impact. I'm sure there is a limit to the number of queries Access will keep in memory and try to reuse.

Colin has done excellent testing databases but In this case, I think the tests missed the point. The tests shouldn't have targeted the number of rows being manipulated as with other testing suites because if you are trying to assess the cost of regenerating the execution plan, the tests would have been better to add 100 rows 10000 times rather than 10000 rows 100 times. Or 10 slightly different queries inside the loop rather than just 1 query. Maybe the 10 queries could each include 10 out of 11 columns with each query dropping just one of the columns which would be enough to make the query different. This would emphasize the looping issue and highlight whether or not Access is reusing the saved execution plan after a few iterations.

 

[ebs17]

In my own tests for the times needed to create an execution plan, I was in the single-digit millisecond range and below. Complex queries, where a reasonably longer optimization time would be an option, are not used as often.
Therefore, you can rather ignore this point and concentrate on the actual formulation of the query (processes and index usage) and the actual movement of data (main task of the query).

 

[Josef P.]

When I use an active DBMS, I avoid creating complex SQL statements in VBA and instead use a procedure or view from the backend.
The reason is not so much because of the performance but mainly because of the quality/maintainability.

Advantage of the active DBMS: nobody without rights can change the view or procedure I have created.
With an access backend, you can now decide between immutability or maintainability. I would say it's definitely a case of "it depends".