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?

 

[BNPMike]

Wow... you're up to 3 stars!!!

OK. I working on it. It seems to me you only have to do two things:

1) create a cross product of teams
2) reorder that to be random

I may just be able to do this...

 

[ESquared]

I have a suspicion that this particular task can be done set-based... but that doesn't mean macros are better than code.

I think we've already adequately demonstrated why code is in most respects superior to macros.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[Darrylles]

Hi,

Just reviewed this 'lions den' of a thread, and saw Richards comments.

The senior business analyst in there sort of 'rings a bell'. Was it really a 'guy' thing or an 'information squirrel' thing?
i.e. His territory, his baby, and when he drops down dead - discard the lot because it's un-maintainable. What a
waste of all that development-time and knowledge, what a waste of company resource - no?
That doesn't matter in this day and age though, because the CEO makes quick cuts, fixes, profits and is elevated,
and leaves some other idiot to pick up the pieces (on-going maintenance of trash software).

Did you actually look at the app. and try to decipher the logic from the endless stream of un-commented, probably
illogically named macros?

I agree with all of your bullet-points, except for: Ruthless effeciency is required when working with large data.
as you have placed it directly after: Macros can be ruthlessly effecient, and in the hands of an expert, a very capable tool.
Macros can be 'ruthlessly efficient' in what sense? In the development time? Surely not in execution time, when compared
to optimally coded VBA.

The problem is, that management get wind of these 'all solving wizards', that use 'macros' that enable non-techs to achieve
the same VISIBLE outcome. It all looks the same, and as management think in graphical terms it therefore MUST BE the
same - until it comes to replacing the 'developer' or problems with speed due to un-normalized tables in conjunction with macros.
Get a pro in to fix it - he/she does not want to know, or costs the company 10 times more than it would have cost to develop
the app. properly - originally.

I say: macros for non-techs and tiny apps. (deleted with no loss). Anything more substantial / useful - VBA. (No mix 'n
match).

N.B. There is one MASSIVE problem which I am not sure applies to macros as it applies to VBA, and that is the
dreaded bug where you copy a form with code across applications and the whole app. is trashed - i.e. 'Network cannot
be found' - error. I do not believe that Microsoft have left this bug intact for so long.

ATB

Darrylle

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

 

[FancyPrairie]

Here's my 2 cents worth...

I think Wizards are one of the better features of Access. CajunCenturion wrote "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." That's when you need wizards the most, to ensure that programmers are forced to follow standards.

I've shown, at the company I work for, that you can do in 1 minute with a well written wizard what would take 10 to 15 mandays to do on a typical medium size project.

The key to developing wizards is to look at your projects and find the common denominators. Then create your own wizards that generate the forms and code behind the forms. In addition, you'll want wizards that generate specific types of comboboxes, listboxes, textboxes, etc. and the code that goes behind them.

Some companies may not be as rigid in their conventions as is CajunCenturion's, so you should develop your wizards so that the code generated fits the coding style of the individual programmer.

When I first started developing a wizard that would write code for me, my first reaction was that I wouldn't want someone to come to me and tell me to use their wizard for generating code. I've been a developer for many years and have my own style of how I layout my code, how I document it, handle errors, etc. I wouldn't want someone telling me that I HAVE to use their style of programming. That's why you would want the wizard to generate code based on the style of an individual programmer or group of programmers. And if their style changes or some code the wizards generates needs to be refined, the wizard needs to be able to adjust quickly and easily to these changes.

Wizards provide user's with a standard look and feel to all of their systems as well as providing programmers with standards that are easy to follow. Your wizards can also scan thru code to ensure that conventions are followed.

It takes a while to develop them, but they're well worth it.

 

[ESquared]

Wizards are useful, but they're not everything. A wizard can fill in the hallways and basic structure, but it's up the programmer to be skilled enough to add furniture and make the layout functional.

I've seen converted-from-macro code that was egregiously nasty. No one's going to convince me that macros and wizards can ever replace competent coding.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[BNPMike]

Right, here's the answer.

I create a table of Teams. In this example they are A,B,C,D,E. Then I create a table of fixtures TeamHome, TeamAway and a field Match. The trick is Match is Autonumber Random.

My first macro runs this SQL:

INSERT INTO FixList ( TeamHome, TeamAway )
SELECT [Team].[Team], Team_1.Team
FROM Team INNER JOIN Team AS Team_1 ON [Team].[Team]<>Team_1.Team;

This has all the matches. All I need to do is present them in Ransom Order. My second macro runs:

SELECT FixList.TeamHome, FixList.TeamAway, FixList.Match
FROM FixList
ORDER BY FixList.TeamHome, FixList.Match;

The output is

-------------------------------------------------------------
| TeamHome | TeamAway | Match |
-------------------------------------------------------------
| A | B | -1930591207 |
-------------------------------------------------------------
| A | E | -770648019 |
-------------------------------------------------------------
| A | C | -659886155 |
-------------------------------------------------------------
| A | D | -459854575 |
-------------------------------------------------------------
| B | D | -1336921602 |
-------------------------------------------------------------
| B | C | -844578606 |
-------------------------------------------------------------
| B | E | -110203414 |
-------------------------------------------------------------
| B | A | 270929469 |
-------------------------------------------------------------
| C | D | 344128935 |
-------------------------------------------------------------
| C | A | 633032378 |
-------------------------------------------------------------
| C | B | 1940827750 |
-------------------------------------------------------------
| C | E | 2007600675 |
-------------------------------------------------------------
| D | C | 1583780075 |
-------------------------------------------------------------
| D | B | 1642351599 |
-------------------------------------------------------------
| D | A | 1999106739 |
-------------------------------------------------------------
| D | E | 2017553960 |
-------------------------------------------------------------
| E | C | -1577411760 |
-------------------------------------------------------------
| E | D | -1314217892 |
-------------------------------------------------------------
| E | B | -501607676 |
-------------------------------------------------------------
| E | A | 1137867640 |
-------------------------------------------------------------
Sorry about the primitive formatting.

So two big reasons why macros are better than code. (It's not macros of course it the fact that macros stop you using code)

1) It's much quicker
2) It's massively less likley you are going to make a coding error.

Thank you and good night!

 

[willir]

Thanks for your comment Darrylle

I am just a small putty cat in this lion's den

Interesting thought on terratorial thing - very perceptive and very applicable. Since I was IT, and he was finance, I felt no terratorial infrindgement.

Yes, I did look at his applications...

- Recognize, learn and appreciate from others. Biases can be a show stopper.

Originally, I developed a predjuce against macros - the "beginner" approach, but upon reviewing his code, I noted that he re-used his macros, and considering the size of his data set, the speed was amazing - ODBC link to an Oracle database to retrieve sales info with 100,000's of records. Extremely powerful and a very friendly user interface. One application was used by dozens of end users all the time.

I still prefer to code, but that does not belittle a developer who prefers to use macros. The real litmus test is the end result.

The one thing the analyst and I had in common is that we both understand "data" which goes a long way in achieving results regardless of the tools.

This "understanding of data" was appreciated by management. The realization was that regardless of the use of wizards seeming to simplify the process of creating applications, etc, applications developed by myself and my colleague were far, far better than other applications developed by those who did not understand data.

Richard

 

[PHV]

I personally think that the real goal is to develop an optimised database schema and write efficient queries.
Who cares the nanoseconds gained by code when, by example, we can gain minutes simply by properly index the tables ?

 

[BNPMike]

Right: my solution is not actually the answer.

I've got a pattern of games but I haven't put them into slots. You've got to play the games I have generated but in what order. In Week 1 I have A playing B who is playing D who is playing C. "Sorry Folks. We are suffering from Total Chaos due to Computer Failure"

How to solve this, without coding. Mmmm...

 

[ESquared]

BNPMike,

I really enjoyed "Thank you and good night!"

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[CajunCenturion]

BNPMike - You're certainly off to a good start, but I don't see any reference to the play dates. I glad you choose 5 teams because that brings byes into play.

So on Play Date 1: A plays B, C plays D, and E has a bye:

On Play Date 2: Who plays whom, who has a Bye?
On Play Date 3: ?

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]

Oh, and may not have been too clear about the random aspect. The random question comes into play in deciding who is Team A, who is Team B, who is Team C, and so forth. You have your schedule using Team A, Team B, and so forth, but in reality, you have to assign a real team to each Lettered Team. The random aspect is in that assignment process -- deciding who is team a, who is team b, and so forth.

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]

Back to the actual discussion.

FancyPrairie - Bringing into the discussion your own developed wizards, rather than using the built-in wizards, changes the question considerably. Up until your post, I was under the impression that we were discussing the built in wizards.

But I’m a bit confused as to just what point you’re trying to make

That's when you need wizards the most, to ensure that programmers are forced to follow standards.

I've been a developer for many years and have my own style of how I layout my code, how I document it, handle errors, etc. I wouldn't want someone telling me that I HAVE to use their style of programming.

Good thing we have those wizards to ensure that programmers are forced to follow the standards, or it it?.

Some companies may not be as rigid in their conventions as is CajunCenturion's,

Since I don’t believe that your on the inside with my company, you may not be aware of our stance on conventions. We are not that rigid in the conventions and standards used on any given project. In fact, it is the project team that decides what their standards and conventions are going to be, unless otherwise dictated by the client. Where we are rigid is that once you establish your conventions that you adhere to them for the entire project. In other words, we’re not that concerned about what the standard is, but that you rigidly follow your chosen standard for the that entire project. I hope that clears up any misconceptions that you may have about my company.

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

 

[FancyPrairie]

Of course wizards aren't everything and can't replace competent coding, but they can save you a lot of time and generate proven, bug free, competent code. I'm, by far, not suggesting that wizards can do it all. Rather they can create the mundane stuff so that the programmer can focus on the main function of the database.

For example, I would venture to guess that every database contains lookup tables. Lookup tables are something that users seldom have to maintain (once they have been initially built), they seldom venture into them (other than via comboboxes, listboxes, etc), and they seldom have to report on them. However infrequent they do these things, they still need the ability to do so. A wizard could create the forms/reports/code for each of these type of tables and add a link to these forms/reports via a menu bar so that the user can select them from the menu. All the programmer has to do is define the table and launch the wizard. In a few seconds he/she is done with it. And can now concentrate on the main functionality of the database.

CajunCenturion, I guess I wasn't clear as to what I meant. Programming styles are one thing, conventions are another. I think of conventions as following naming standards for various objects (form, reports, controls, etc.), folder layouts, shares, and headers that describe the function of the module, author, revision dates, etc. Programming styles are how you layout your code, indentation, error handling, etc. Consequently, when you're generating code it must follow conventions while at the same time, generating the code that is the style of an individual programmer. And each programmer defines his/her style for the wizard to use. Or, as a group, you can define the style of code that the wizard is to generate. It's up to the team.

I don't know how rigid your conventions are at your company. It doesn't matter. As I stated before, the wizard generates code based on the standards and styles defined by the individual or group.