The pitfalls of VBA proficiency

[isladogs]

Ah! I think I now understand your previous comments about action queries which I misunderstood before. I've deleted my previous reply
AFAIK the utility should convert any query sql to VBA ...or vice versa …. optionally including creating the query itself.
I haven't tested with some types of complex queries such as non-equi joins but I've not had any negative feedback so far.

 

[Thales750]

Back to the topic.

Code is like a 2X4, or a plumbing fixture. Different methods or preferences are like different brands of the same kind of tools.

Pretty much it's what you make out of them that counts. Having never been a "coder" my sense of pride comes from the end results not the methods. And when I search the web to find new building materials, I will as often as possible use the ones with the fewest lines of code.

I wrote a Sort routine once, one of the few completely original procedures I ever wrote. One of the guys here saw it and offered a criticism having to do with reloading data-set for each record. It was a valid criticism, in a system with a lot of records it would bog fast. It turns out I only used it on low record count tables, so no need to change it.

What a big surprise when I finely did, it took most of a day to rewrite all that. But what a difference it made. So I say use whatever is the easiest to implement, until it conflicts with system performance. And then use that new one over and over.

 

[The_Doc_Man]

I have to admit something. When I started my big project with Access for the Navy, I built code just to get things up and running because the SQL UPDATE query that I needed was at that time outside of my comprehension. Remember, I started as an old device-driver man and experimental monitoring man for which code was the only way to go.

Once I learned a bit more about layering queries and about multi-table joins, I started using really huge and ugly queries for reports. But they worked! Eventually I learned how to do UPDATE and INSERT queries. When I did that, the performance of my project jumped so noticeably that a lot of my user base commented on it. Eventually I started thinking in the double-negative way of isolating things a piece at the time by running nearly the same query differing only in which fields would be updated. Which was where the layered query approach kicked in. Once I learned to make ALL types of queries my friends, my project because a lot easier.

Pat's way perhaps isn't EXACTLY what I would always do - but it is close enough to what I did for the last 10 years of my career that I won't quibble over the difference. I'm just enough of a pragmatist that I will still code up-front if the problem is simple enough and the implied loop is short enough and the SQL would be convoluted enough.

 

[gemma-the-husky]

Just a thought.

! was told, in general, the answer is a query.

I use queries wherever possible. I use code, when I can't do it with a query - perhaps the query is non-updatable.

It's hard to get the precise percentage, but a good deal of my code is defensive - controlling the look and appearance of the dbs, checking for legitimate values, confirming that user input and so on.

 

[Thales750]

Just a thought.

! was told, in general, the answer is a query.

I use queries wherever possible. I use code, when I can't do it with a query - perhaps the query is non-updatable.

It's hard to get the precise percentage, but a good deal of my code is defensive - controlling the look and appearance of the dbs, checking for legitimate values, confirming that user input and so on.

The reason for VBA queries, whenever possible, is so you don't end up with a 1000s of querydefs.
My queriesdefs now look more like tables with very few, to no conditions. Then VBA Code makes them specific. Streemlines the whole process, and makes it where you don't end up with Querydefs that do the same thing as others and 50 names that all sound the same.

 

[isladogs]

This article may be of interest Query vs SQL vs QueryDef

It compares the speed of each method for a wide variety of query types.
However speed isn't the only factor in determining which method is most appropriate and in the end it largely comes down to personal preference

EDIT
Oops. I seem to have already mentioned this article back in posts 8 & 10

 

[gemma-the-husky]

what I really meant was, that if you can mange the data with a query, it's generally much more efficient than iterating a recordset. Sometimes you have to do the latter though.

Obviously you have to use code to manage your interface, and for a lot of data validation.

 

[The_Doc_Man]

! was told, in general, the answer is a query.

This is off-topic, but my wife has this 'fridge magnet...

"If the answer is chocolate, who cares what the question was?"