Don't get me wrong... I'm not bad mouthing your code or your article... I actually gave it a good mark. 😀
There's only one item on a list of benefits that you asked for that I can think of...
1. Users become self supporting
Most users can "survive" in MS Excel. Most of them can write simple formulas to accomplish what they need to accomplish. "Single button automation" with VBA is a really cool thing, but most users can't even spell VBA never mind do something with it other than recording simple macros. The end result is that whenever the users want a change in the automation or want something new to be automated, they have to come to you. You become the very busy single point of failure because, as you already know, everybody likes to make it easy on themselves and your fine automation definitely makes it easy.
If you get those folks together for a "Lunch-n-learn" and show them how to both setup MS Query and use "Get External Data" through a View to do their jobs, your users will become self supporting (their pride in doing something really cool will see to that) and you can concentrate on your primary job as well as thinking of what to teach them next. If you limit what they can read to simple views, you also keep control of the queries so they don't crush the server performance wise.
About the "Lunch'n'Learns"... not everyone actually needs to show up for those, although they should all be invited. We had a huge problem with users writing some really ignorant queries where dates were concerned and they were not only taking a million years to run, the results were often incorrect because the didn't actually know the right way to use dates (and all it's abbreviated forms) as criteria. I created a nice little "How to" booklet and not only gave it to them as handouts, but published it on the company WIKI. Only about 30% of the people actually attended the course but 100% of the people learned how to do it correctly because the people who attended the course actually taught it to their peers.
You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient. And, I've found, that if you teach them, you also and very suddenly become very well known and indispensible as a source of knowledge. That helps a lot in today's job market. 😉 Hmmm... so maybe there's other advantages on the list of benefits you wanted, eh? :w00t:
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)