Settle an argument: To code or not to code? 7

[swpsoak]

Two of us at work use Access a lot. From tracking vacations and sick leave, to a lubrication database, work order tracking and so on.

I learned Visual Basic (self taught) several years ago and as a result when I need to open a report, close a form or perform just about any other action, I code it in the Event Procedures; “On Load, On Click, On Exit” and so on. I even do my SQL statements in the Private Subs.

My co-worker doesn’t know VB and SQL so he depends on the Wizard functions to accomplish what he needs to get done. He will write Macros to perform his needed action, where I will write code. He will run queries, where I will write a SQL statement. Granted he can see the SQL statement after he creates the query, and understands it, but doesn’t write them from scratch.

Our databases serve us well and there’s not too much both of us can’t do with our databases.

We had a discussion the other day about my coding verses him using the Wizards, macros and queries. He claims that it works just as well as mine (which it seems to). I claim since I am not creating additional step(s) with macros and queries, that mine is more efficient and faster. I claim that the built-in created functions may put in code that is unnecessary.

I have seen questions on the forum where people don’t know how to code, so the wizards are great for them. But the answer to my question may provide incentive for them to learn some VBA to get better performance.

What do the experts have to say?
To code or not to code?

 

[CajunCenturion]

As a software engineer, I am responsible for whatever applications I get involved in, and the user community naturally expects good quality working software. I do want to accept the responsbility of what some wizard does. I code it.

I want to ensure that the software does what the users need it to do, nothing more, nothing less, and not what some wizard thinks it should do. I code it.

I need to maintain the code over the life span of the application. I have no desire to maintain some wizard's code. I code it.

I want to, as much as possible, shield my application of the vagaries of MS when they decide to issue a patch or upgrade. I code it.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Darrylles]

Hi,

Not an hard question to answer: use code EVERY time, for EVERYTHING and NEVER use macros.

No offence to your colleague (or secretaries), but as far as I am concerned, macros are provided for secretaries with little technical knowledge.

Code allows for structured, modular programming. It is almost self-documenting and is easy to comment where things get complex.

All things being equal: you give a coded app. to a developer, and he will understand it in about 5% of the time it would take to unravel an app. that uses macros only, AND be able to re-use existing code with ease.
This makes the app. easily maintainable.

As I've said, macros play their part for non-technical employees to enable them to 'knock together' a quick app, but in any serious application: don't use macros AT ALL.

There is one exception where a macro call HAS to be used, but it's that important that I forget exactly what it is.

As for SQL statements in code versus queries - I use both and each has it's valid uses.

That's my two-penneth-worth (for what it's worth).

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[swpsoak]

My thanks to Darrylles and CajunCenturion for their prompt and insightful comments. Interesting.

This reinforces my point of view; code it if possible.
To those how wish to use Access to its best potential, learn some VB/VBA. I think you will like what you can do with it.

But as Dennis Miller says…”That’s just my opinion, I could be wrong”

 

[Darrylles]

SWP,

In this case - we are not wrong (it is not possible).

The advantages are too massive (bearing in mind - 'knock together' and 'application').

In any case - 'knock together' with code only. ;-)


Kind regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[Darrylles]

Cajun,

You say WHAT you do, but not WHY you do it that way.

The question can be assumed to mean 'Which is the best method?' Anything to add?

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[RoyVidar]

I claim since I am not creating additional step(s) with macros and queries, that mine is more efficient and faster."

I would not be surprised if you, when testing, found that your co-workers, work was more efficient and faster.

Reason: Stored queries are generally faster than using/assigning SQL strings.

You'll find little or no disagreement on macro vs code (Darylles "rant" is entertaining;-)).

But regarding the wizards?
I do use several of them (forms/reports). Especially when prototyping, but also in developing. Those I use, often create a good starting point. I've found that to reduce development time. Most of the wizard generated code need some tweaking, according to the requirements of the application, get the needed control over what it does, but might still often be faster than writing from scratch.

Code if possible? Over macros - yes, but as a general rule No, I
[ul][li]Use code when appropriate[/li]
[li]Use other means when appropriate (but not macros)[/li]
[li]Use wizards (and amend) where it can save development time[/li][/ul]
- evaluated according to the requirements

Darylles
"The question can be assumed to mean 'Which is the best method?' Anything to add?" - though I'm not CajunCenturion, I'd offer the following:
The best method is the method meeting the requirements.

If the requirement were to produce a report by tomorrow noon, and your job depended on the result, who would say "Sorry, that's just not the way I program"? I would, at least say something along the lines of "Yes, but do keep in mind that this is a quick and dirty job, which would need to be amended to be of future use".

Roy-Vidar

 

[BNPMike]

swpsoak

As RoyVidar points out, queries are generally faster than SQL because the query also stores the plan (the program that actually accesses the data) whereas SQL in a VBA statement has to be 'compiled' each time it runs. This of course is not what your question was about but does demonstrate that things are never straightfoward.

You can achieve an awful lot with just inbuilt functions (eg wizards) and macros. Also you can't avoid coding. You need to control your SQL. The thing is you can write very complex SQL nowadays so things you would have done in recordsets in days gone by can now be done in the query.

I'll go against the flow and say if it's a simple application try and write it without VBA, at least for as long as possible. It's good discipline to try and solve the problem without resorting to code. There aren't too many downsides to using macros. The big one, sadly, is people like Darylle will think you're an amateur which is why first thing tomorrow I'll be writing VBA and not macros. Not a logical reason but a powerful one

 

[jrbarnett]

The major disadvantage of macros over VBA code is that macros cannot have any in built error trapping code. While it is quite possible to write VBA without error trapping and handling code, it is not possible to put such facilities in macros. This is why I avoid macros wherever possible.

John

 

[sha76]

I'd agree with Darrylles that applications written with macros are much harder to understand, in fact if I have to take over a database which uses macros I will change everything over to code, for my understanding and to make it easier to maintain. The only macro I ever use is to switch off the hourglass when my code errors during development.

Although I completely agree with everything put forward in support of coding, I've always coded & never actually used macros, so I'd find it interesting to hear the opinion of someone who has.

"Your rock is eroding wrong." -Dogbert

 

[CajunCenturion]

You say WHAT you do, but not WHY you do it that way.

In which part of my post do you have a question about the Why?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[2drunk2funk]

I am from a coding background and was at a company for many years where applications (not just access) had to be user idiot proof in every respect.
Literally controlling every entry in terms of validity and data relationships (A value in one field governing possible values in others etc etc)

I use the wizards for some of the basics but always end up coding most of my access applications.
To give them a user friendly interface and make the application work for them instead of against them.

Coding wins everytime for me.

 

[Darrylles]

Hi again,

Hey - I'm just a misunderstood person . Sorry if I sounded like I was 'ranting'.

I've got to be honest and say that I am biased because of an experience that I had some years ago, when I took over the admin. side of an engineering company ERP system developed in MS Access and totally macro driven.
There were a large number of mostly non-formalized tables with no schema/model.

I suppose that it was a 'good' experience, because it taught me how to use (and not use) macros (I had never really looked at them before). It also taught me how advantageous VB code is over macros.
I'm a great believer in 2/3rds code - 1/3rd commenting.

I really do think about the person in the future who will maintain / upgrade the app. 'Information squirrels' really annoy me (protecting their little patch of territory and making it intentionally difficult for future upgrades).

I also suppose that there may be a way of using nothing but macros in a large application in a way that allows for an easily maintainable app. - but for the life in me I can't see a way.

BNP - I've got to agree with, but nowadays when a manager has seen you 'achieve' the end-result in double-time - they will never let you off that hook and you will be forced to become a 'quick 'n dirty' developer.
If I can avoid the quick 'n dirty - I do my utmost to avoid it. i.e. "Yes, you can have the report in 10 mins, but I will take me an hour later to upgrade it to the correct spec."
Also, I have NO argument against wizards - I use them all the time for buttons etc (then enter the code and edit it) - just not for tables/forms.

Also, do not learn to code with VB 'because people think you're an amateur', learn to code with an open mind an then come back with an opinion.

Why you think that you should leave out VB code for as long as possible is beyond me: do you really want to hide your logic away in untraceable macro's?

CAJUN:
I do want to accept the responsbility of what some wizard does. I code it. Why?
Use the wizard, then read the code: THEN accept the responsibility of that code. Why do you feel the need to re-code? Is your code better, clearer, more commented, quicker execution, more easily understood?

Ditto for line 2.
Ditto for line 3.

Line 4 - you give a reason.

I wasn't being picky, but if I was arguing for macros - I'd have to ask you why you did these things.
--------------------------------------------------

Sorry for any offence caused to anyone - not intentional.

Regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[CajunCenturion]

Among the reasons for not using the wizards, is that I primarily work with rather large projects, which are engineered using tried and true program development methodologies. I've yet to find a wizard the generates code that is consistent with the system standards, function naming standards, programming standards, documentation standards, variable naming standards and so forth. All of these have to be re-written in order to achieve conformance. It's faster to write it correctly the first time. This can really make a difference when you have to go back to that code a year later.

The wizards don't know what I want to do. They do some things that I don't want done, thus those parts have to be removed, they don't provide code to do some of the things that I do want done, and they have to written. Bottom line is that it takes longer to "correct" the wizard's code than it does to write it from scratch.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Error handling is another apsect that with both macros and wizards, falls way short of meeting requirements and specs.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Darrylles]

Cajun,

Cracking 'why's.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[BNPMike]

Darrylle

Why you think that you should leave out VB code for as long as possible is beyond me: do you really want to hide your logic away in untraceable macro's?

The first reason is I'm a great believer in using the minimum number of functions to achieve an end result. VBA allows you to do whatever you like. That can tempt people to jump in and start coding rather than spend some time trying to think of a clever way of doing things much quicker. The macro facility provides you with almost all the functionality of Access. Combine that with the power of SQL and you should be able to do a whole load of applications without requiring 3GL programming.

The second reason is if you were asked to design an interface you would probably come up with macros. You hardly have to remember anything, particularly arcane VB constant names for function parameters. I know the IDE helps but why not just fill in a form that is tailored for each function - oops that's macros.

I have to confess I am not an Access developer. I just have used it for things for years. I needed to change some functionality on a corporate-writtem small app yesterday and my VBA broke for some obscure reason so I had to use macros. It felt OK as I had an excuse, but perhaps I shouldn't have to feel guilty about using them.

 

[CajunCenturion]

I will agree that there are certain situations where macros can be used effectively, but VBA provides considerably more flexibility, more maintainability, and in general, more power.

Can a macro return a value like a VBA Function can?
Can you iterate through a Recordset with a Macro, processing each individual Record?

Some other things to consider.

Macros are separate objects, in other words, they are not associated with any specific form or object. VBA Methods and Events are attached to its "parent" object, be it a form, report, or class. The advantages are that if you copy a form, all of the methods and events get copied with it, but none of the macros. Making a change in a Form method will affect only that form, but making that change in a Macro will affect everything using that Macro. Which is better? In some cases the macro, but it most cases, the macro change will lead to undersirable side-effects at seemingly unrelated aspects of the application. In this category, there are pros and cons to both, but on balance, and in the long run, the individual control provided by VBA will be safer.

VBA provides access to a number of external objects (FileSystemObject, ShellScript, ADO/DAO for multiple external databases, and Regular Expressions just to name a few) that macros cannot provide. VBA provides access into the windows API that macros do not. The API, which is a very under-utilized asset by most programmers, provides access to considerable power and functionality that can only be reached through VBA. Add to that the ability to use 3rd party ActiveX controls. Can you use ActiveX with macros?

We've already talked about Error Handling, so won't go into that.

VBA provides, via the cascading If-then-else statement and the Case statement, conditional based processing that would extrememly difficult (if at all possible) in many cases to achieve with macros.

Then there are custom paramaterized functions and routines that provide the VBA programmer much more flexibility than you have with Macros.

I'll stop here, for now, as this post is long enough.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ESquared]

The monthly invoices at my company are produced in Access and were developed using... macros. Trying to figure out what the macros are doing is a NIGHTMARE. You can't debug, you can't set breakpoints or step through code, you can't loop, you can't this, you can't that, you can't the other. That means that they use a gazillion if statements and call a bunch of other macros. Trying to follow the chain of logic for each possible variation is crazy.

The person who wrote all these macros spends MULTIPLE HOURS making upgrades and fixes that should take 5 or 10 minutes. I personally don't even want to touch the things and hope I'm never called to. (I am so glad that I'll be rewriting our entire billing and invoicing system soon... in SQL Server as much as possible.)

Properly written code is so much more powerful than macros. Try writing a generic form resizer in a macro. Try connecting to other databases based on connection settings listed in a table. Try hashing passwords.

I could go on and on...

 

[willir]

Interesting....

I had this very same conversation with a senior business analyst -- he used macros and I use both but prefer VBA.

He had some very empressive applications, but admitted that he had a problem that had plagued him for months. (He never asked for help - a guy-thing I guess)

(And FYI, his applications were way beyond that of a admin assistant - for example: allowed the end user to select complex financial reports from an Oracle database with 100,000's of records)

When I sat down with him, I solved this problem and others within minutes for him. A real simple example:
He wanted two combo boxes. Criteria in the first combo box determined the criteria for the second combo box, or change the contents of a subform.

In the end, we both agreed (a rare thing)...
- Work with what you are used to.
- Recognize, learn and appreciate from others. Biases can be a show stopper.
- VBA code tends to offer more power, more control, more flexibility.
- Macros can be ruthlessly effecient, and in the hands of an expert, a very capable tool.
- Ruthless effeciency is required when working with large data.
- VBA code defintely adds power to forms and reports, and definitely provides a cleaner user interface, better error handling and data validation.

Richard