VBA vs Queries

[TheDeceived]

I'm currently trying to argue a point to a friend at work about driving my entire database through VBA instead of using the built in Query Designer within Access.

I'm using the combination of DAO.Recordset library files and hand-built SQL strings in VBA to access fields from my tables instead of using queries within Access. I prefer to power everything in VBA and let Access handle the table/form design.

My friend is of a different persuasion. He feels that I should be using the Query function within Access to pull through data from my tables as they're easier to edit and can be followed/editted by those who do not have VBA experience (I totally agree with the latter arguement).



I just can't logically think why you'd want to let the data fetching be handled completely differently to how the form is mainpulated (i.e. moving embedded subforms/listboxes/combo boxes etc around)? The way I see it:

Tables/Back End -> VBA/Middle Man -> Forms/Front End

By using queries, you just add another layer to the entire process that just complicates things.

I know they both achieve the same end goal, so this arguement is benign but I curious as to what you guys think is the best approach.

[weekend00]

VBA code is much harder than query.
that mean it is much harder to do the code and there is more change to make mistakes.

[SteveH2508]

One word - speed. Queries can use Rushmore optimisation to work out the most efficient way to run the query - VBA does not.

[pbaldy]

Two points. First, a saved query gets compiled and will run faster than the same SQL run from VBA. Not saying how measurable that speed difference will be, but it is there.

Second (and I'll preface this by saying I'm no expert on the inner workings of Access), I think the "data fetching" is being handled by JET either way, so your argument that it's being handled differently than form manipulation is probably misdirected. Either way (saved query vs VBA) SQL is being passed to JET to do the actual work.

My personal rule of thumb is to use saved queries most of the time, SQL built in VBA when the actual structure of the SQL will change based on user input.

[ajetrumpet]

Deceived,

I agree with Paul. And addition to that, there was a poster on another big internet forum named Pat Hartman. She once said that she hates writing code unless absoultely necessary. I agree with this too. Saying that comes from years of experience and I don't doubt it one bit.


The other thing that hasn't been mentioned yet here is the future outlook of Office. It is no secret that MS is trying to shift their products to be more Macro based rather than code. And while the financial incentive for them obviously stems from the fact that the majority of their users have no desire to learn code (mostly because they don't have time or don't understand), it IS fair to say that at some point in the future, Office very well could be released without access to VBE's at all. I'm sure that is not anywhere in our near future, but if you plan to be in business for awhile, aligning yourself to what MS's plans are obviously can't hurt. Control is ultimately in their hands anyway in terms of compatibility and other things..

[pbaldy]

I don't think VBA is going anywhere. The sense I gathered from the Access team is that macros were enhanced for use with the new 2010 web front end. You can't publish an object that uses VBA, only macros. This dealt with performance and security issues; with macros, they can control both better. With VBA, I might publish something with malicious code or some goofy million-record loop that would tie up the server and kill performance for everybody (with a web app, that processing is happening on the server, not the PC).

With data macros we've also been given something akin to a SQL Server trigger. They've made it more powerful, not less, so I don't feel like they're trying to push people up. Maybe I'm just naive.

[blah]

code gives a developer what every developer needs: control over the construction of his product.

All the arguments I've read here (and have heard from people who know just enough about software to be counterproductive) presume the superiority of the canned objects and routines and the Access environment which are, by the way, just more code. Compiled code, I'll grant you, but it's still code. Access is the database for Everyman and while that's cool and it's allowed Everyman to think he's the King of Information it is by definition institutionalized mediocrity.

I will agree that Access treats VBA like a red-headed stepchild. If you conform yourself to nature of its objects - forms, for example - you can do upper-level mediocre development that will impress the average MIS manager. But you cannot, you will not, ever develop anything that rises above Access' multiple, disfunctional personalities -- personalities that are only bound to get more disfunctional with age. And, ironically, getting to the upper level of mediocre development with Access often requires coding bits that would make any programmer feel like he needs to get Absolution. The watchword for Access development is "workaround."

So if you are the sort of craftsman who likes to choose his materials and build an excellent product, you should probably find another platform, as I am. My next foray will be into Visual Studio because I have it on my laptop and can try out .NET. If not that, perhaps Oracle.

If you're the sort of operator who thinks building subdivisions in the suburbs is the way to go, then your love for the Access environment will serve that end. Until it doesn't....