Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-05-2019, 03:30 AM   #1
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
The pitfalls of VBA proficiency

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?

__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 10-05-2019, 03:48 AM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: The pitfalls of VBA proficiency

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.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-05-2019, 05:10 AM   #3
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,895
Thanks: 57
Thanked 1,289 Times in 1,270 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: The pitfalls of VBA proficiency

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

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-05-2019, 05:39 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,572
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: The pitfalls of VBA proficiency

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
NauticalGent (10-09-2019)
Old 10-05-2019, 08:11 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: The pitfalls of VBA proficiency

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-05-2019, 08:49 PM   #6
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,085
Thanks: 10
Thanked 220 Times in 208 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: The pitfalls of VBA proficiency

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?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 10-08-2019, 10:39 AM   #7
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: The pitfalls of VBA proficiency

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.

__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 10-08-2019, 10:56 AM   #8
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,018
Thanks: 114
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: The pitfalls of VBA proficiency

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/s...s-6/4594478795
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns – the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 10-08-2019, 12:18 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: The pitfalls of VBA proficiency

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-08-2019, 12:43 PM   #10
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,018
Thanks: 114
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: The pitfalls of VBA proficiency

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 ….
Attached Images
File Type: png Capture.PNG (50.3 KB, 196 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns – the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Galaxiom (10-09-2019)
Old 10-09-2019, 06:24 AM   #11
Wayne
Crazy Canuck
Gold Supporter
 
Join Date: Nov 2012
Location: Toronto, Canada
Posts: 159
Thanks: 22
Thanked 16 Times in 14 Posts
Wayne is on a distinguished road
Re: The pitfalls of VBA proficiency

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
Wayne is offline   Reply With Quote
Old 10-09-2019, 07:04 AM   #12
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: The pitfalls of VBA proficiency

Quote:
Originally Posted by Wayne View Post
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!
__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 10-09-2019, 12:19 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: The pitfalls of VBA proficiency

If you feel the need to code first rather than last, just call me. I'll talk you down off the ledge
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
NauticalGent (10-09-2019)
Old 10-09-2019, 02:01 PM   #14
Foster2019
Newly Registered User
 
Join Date: Sep 2019
Posts: 5
Thanks: 9
Thanked 1 Time in 1 Post
Foster2019 is on a distinguished road
Re: The pitfalls of VBA proficiency

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
Foster2019 is offline   Reply With Quote
Old 10-14-2019, 10:25 AM   #15
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,042
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: The pitfalls of VBA proficiency

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.

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pitfalls of Family Research The_Doc_Man The Watercooler 22 07-06-2018 03:10 PM
Maintaining proficiency - expiry of count spikepl Queries 4 05-26-2014 01:30 PM
Data model to specify and track proficiency/currency spikepl General 2 04-13-2014 07:29 AM




All times are GMT -8. The time now is 06:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World