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.

 

[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

 

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

 

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

 

[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

 

[Thales750]

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

Did you make yours work with action queries?

 

[Thales750]

Not for the main functionality.
There is one UPDATE statement but that's only where users want to save the combo setting on the VBA tab as the default.
Suggest you look at the code if interested in knowing more

I think if someone were "of a mind", a combination of the things you did and what Gina did would be very useful.

 

[isladogs]

Hi thales

Not quite sure what would need combining.

I didn't know Gina had done her own version until I saw your link.
If you download mine you'll find it includes all the features in Gina's version and does a lot more as well.

 

[Thales750]

I didn't realize you had added the action query part.

I will take a look at your version, thanks.