The pitfalls of VBA proficiency

[NauticalGent]

Before I begin the rant, let me say that calling myself proficient in VBA is a HUGE stretch...but...thanks to this forum, my skill-set has vastly improved.

So much so that I find myself writing code first before trying to do things in native Access. There was a time that writing an SQL statement was not even an option and I would have used a query or even a parameter query. But now it has become my first option and not my last resort.

Not saying that it is wrong, but I spent all day yesterday trying to do complicated "stuff" with LDAP's and List Boxes when using a simple RecordSet from a query would have gotten it done in a fraction of the time.

I can only guess that my imagination gets the better of me and I get carried away with the coding that I don't stop to think "is this the best way?" Concentrating on seeing if I can do it for the sake of doing it instead of doing it the way it should be done. The whole "smarter not harder" thingy...

Is this normal or am I unique in my stupidness?

 

[JHB]

I think it is normal. We are too busy to get our idea to work, that we can't see the forest for all the trees!

A break does wonder, is my experience.

 

[theDBguy]

Hi John. I think it's normal when we learn something new to have a tendency to over use it.

 

[The_Doc_Man]

John, there is an old rule: When the only tool in your tool box is a hammer, everything had better be a nail. By adding other methods to your tool box, you gain variety over when to use each different skill. Experience, on the other hand, is why your DON'T select the screwdriver when you wanted the paint scraper.

Chalk up your fight with LDAP and List Boxes to (a) learning, and equally important (b) experience.

 

[Pat Hartman]

I came to Acess with 25 years of programming experience. Access was a different animal than COBOL and CICS/IMS and the hardest hurdle for me was to get a grip on event code. However, what attracted me to Access was the realization of what I could do without any code. By that time in my life, I'd already written my million lines of code and I certainly didn't need the practice so I trained myself to do things the Access way.
1. Create an action query as a querydef to do bulk updates rather than a DAI/ADO update/insert loop
2. Use property settings and conditional formatting before event code
3. Use event code
4. Write a procedure or function in a standard module.
5. And last on my list when the BE is SQL Server is to make a view, a pass through query, or a stored procedure.

Most people avoid code because it scares them but once they've written some code they are loath to replace it with something more simple. In their minds a line of code written is a line of code that must be preserved forever. I avoid code because it takes more time to write code to do it "my" way and it is rarely more efficient or easier to test. If I write code that is too complex or doesn't work quite as I wanted it to, I comment it all out and then I delete it once the replacement works. I almost never keep dead code.

It's mind over matter. Never be afraid to simply step away from the keyboard and rethink what you are doing.

 

[Deleted member 147267]

NG I think we're a bit alike in that respect. I seem to gravitate to code for a few reasons

- I find the whole object model challenging and strive to be more familiar with it
- I haven't mastered sql concepts because the query GUI is too inviting for unmatched and find duplicates, plus subqueries hurt my head. Those always require web examples as a starter. Sometimes code becomes the "obvious" choice for complicated querying when you don't see an alternative. Have to say that some people here and elsewhere have amazed me with what they can do with complicated sql!
- I might as well try to learn Martian as RegEx, although sometimes I can work with examples. But until recently, code was the default approach
- there are just some things you cannot do with the built in GUI portions, e.g. enumerate over a collection, use application dialogs, alter a property, etc. and I find that stuff more intriguing
- then there is the that fact that participating in forums helps me to keep a modicum of proficiency about Access. However it's no longer part of my job (retired) and it seems the prospect of using it in any form of employment slowly continues to fade away, so why not play with what interests me?

 

[NauticalGent]

Excellent feedback guys, it is reassuring to hear from developers of your caliber experiencing the same issues.

Pat, your “Access Way” mantra is always appreciated!

Thanks again for y’all’s time.

 

[isladogs]

Pat
Regarding your 'Access way' point 1, would you care to explain why you use query defs for action queries rather than using update/insert sql statements or saved queries
I ask because my tests indicate using query defs is almost always slower than either of the other two approaches. Whilst the differences may not be that large, the results were consistent.
See http://www.mendipdatasystems.co.uk/speed-comparison-tests-6/4594478795

 

[Pat Hartman]

Colin,
When I was working with DB2 in COBOL, I used to dream of a tool like the QBE. Something that would build queries for me without all that typing. When I was working with COBOL, it was pretty much one project at a time so I was usually able to keep the schema in my head, especially if I built it so I would just "know" if we used Customer or Cust or CustID and so could type it without making a lot of mistakes or even typos. But when I switched to Access, my projects were smaller and I frequently have multiples going at one time. I'm also getting old and have lost a lot of those brain cells that helped me to remember hundreds (even thousands) of table and column names accurately. Not so much any more. So, I'm back to using QBE at least to build the select clause and anything else that is better done in the GUI than in code. Once I build and test the querydef, I don't have any need to convert it to a string so I leave it as a querydef.

I'm not sure what difference there is between querydefs and "saved queries" in your question. Either the SQL is "embedded" in the VBA or it is a querydef. Even the RecordSources of forms and reports and the RowSources of combos and listboxes are saved as querydefs (with ~ as the first character BTW).

Code loops that update/insert row by row are always slower (sometimes hugely so) than any other method so we are left with using Access to execute a querydef or SQL String or using DAO or ADO to execute a querydef or SQL String.

I normally use DAO to execute my querydefs, even when they take parameters which I have to set prior to do the Execute command. Sometimes when the criteria is complicated and variable, I do build a string and then run the string rather than a saved querydef.

In the past, running a querydef was more efficient than running sql code because the first time a querydef runs, Access creates an execution plan and saves it. After that, Access uses the saved execution plan. When you run an SQL String (regardless of how you run it), Access must create an execution plan on the fly and has no place to save it. In addition to a small amount of time overhead, this used to cause huge bloating. Access has solved the bloating problem so now we are left with minor differences in execution time. In my apps, the time difference has never been noticeable so I never stopped using querydefs.

 

[isladogs]

I think the easiest way for me to respond is to ask you to read my article and possibly to run the tests using the sample database accompanying it.
As you quite rightly say, a query execution plan is created when a saved query is first run and reused whenever the query is run subsequently (until the database is compacted). Despite that, executing sql statements is often faster and executing/running query defs slower.

Whilst the time differences are usually fairly small these days, the text results were consistent across a range of different types of 'query' and on different workstations. But in the end the differences are usually small enough that personal preference is the clinching factor. In my article I wrote

Horses for courses ….

 

[Wayne]

I was in the trucking business, and knew nothing of coding or even Access for that matter. I tried using Excel for revenue/expenses tracking, but since we operated in both Canada and the US, and in two different currencies, Excel was not the answer. A friend told me I needed a database, and I bought FoxPro (back in '95). Bought a couple of books, and we were off to the races. In '98, i made the switch to Access. I bought some more books and learned as best I could. Things were okay, but my attempts at automating things were, to say the least, not going well. My OCD was pushing me to do better. I visited this forum on a number of occasions seeking answers, and finally joined in 2012. Last year, I started the first of my support donations, to give back to the forum that has helped transform my basic Access skills into so much more. And I try to help others when I can.

All of you have helped me learn so much, and today, we have a great working database. Thank you so much - all of you.

That being said - Nautical Gent, you hit the nail on the head. Lately, I keep overthinking things I am trying to create, when there are simpler, easier answers right in front of me. But, as the old expression goes - the best place to hide things is in plain sight. Kinda makes me feel like I am trying to re-invent the wheel at times!

Wayne

 

[NauticalGent]

Lately, I keep overthinking things I am trying to create, when there are simpler, easier answers right in front of me. ... Kinda makes me feel like I am trying to re-invent the wheel at times!

The struggle is real, and you are not alone!

 

[Pat Hartman]

If you feel the need to code first rather than last, just call me. I'll talk you down off the ledge

 

[Foster2019]

I always go with the method I think first. Trying to do something I know should work in theory, and sticking to it even if half way through I've thought of a quicker or easier way. Is how I've learned to do most of the things in Access. Once completed, I try the other way to. I think that's normal, right?

#2Stubborn2Quit

 

[Mark_]

John,

My first question is always "Will this be what I need or will I have to prepare for expansion later?" If it is something I KNOW I will need to expand upon, I plan how that will work first. This often is because I figure out that the "One time" or "Just this" request is actually going to expand once the end users get it.

Once I know that it needs to be enhanced later, I make a couple note on HOW that will need to happen, thus giving me the answer to "Code or not to".

This also means I seldom write the actual code that is needed for a given control in the embed for that control. Rather I write a sub that is intended to Take Care Of (TCO) ... SOMETHING ... that gets called. This approach helps me avoid coding when it isn't appropriate while using coding when it will be required.

 

[kevlray]

I just got 'finished' (for anybody who has done a lot of coding, you know what I mean) making a series of macros for a dairy testing tracking. I did not create the database, so I have had to work around some things that might have been done differently. But there are a lot of rules about when a dairy fails a test (there are five tests in all) and what to do. Since my coding skills are a bit rusty, there has be a lot of back and forth re-doing code (hopefully more efficient). I seriously doubt I am done with the code, but it appears to be working correctly at the moment.

 

[Pat Hartman]

Terminology matters when you search for help so I'm going to be pedantic.
Macros are not code, especially in Access. Macros written in Word and Excel generate VBA code so sometimes people who work in that environment call macros "code" but in Access, they are completely separate animals.

making a series of macros

If you wrote VBA code rather than built macros, you might say

wrote code
or
wrote some procedures
or
wrote VBA code
or
wrote functions

Procedure is a generalized name for a set of code that is executed as a block. A Function is a Procedure that can be used as a variable because it can return a value. In VBA a module is a collection of functions and procedures and declarations. It cannot be executed and cannot have the same name as any procedure or function defined elsewhere in the application.

Me.SomeField = MyFunc()
Call SomeProcedure

Half the battle of getting help is knowing what to call things

 

[gemma-the-husky]

I think another issue in larger applications is keeping track of code, and making it re-usable

Sometimes, although you know you have written something to deal with your current problem, it's quicker to do the same thing over again, then try to find the original bit of code.

 

[Thales750]

I think another issue in larger applications is keeping track of code, and making it re-usable

Sometimes, although you know you have written something to deal with your current problem, it's quicker to do the same thing over again, then try to find the original bit of code.

I built a database to organize my code. With key word searches and two layers of parent tables to filter it.

Over the years it has saved hundreds of hours.

For embedded SQL Strings I use Allen Browne's and Gina Wipp's converter.

He made it, she made it better.

https://access-diva.com/blog/?p=241

 

[isladogs]

I also enhanced the original utility by Allen Browne so it now works 'both ways': SQL to VBA and back again