Solved Insert Query vs VBA Code

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

 

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