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]

Can you iterate through a Recordset with a Macro, processing each individual Record?

This is the show stopper - the most fundamental point.

As far as I can see you can do most things with macros that you can with code, maybe not in quite the same way. Except of course record processing, as macros only have SQL to work with. So the question is "In a typical relational database do you really need to process records?"

Put it another way: if you have some code that iterates through recordsets, will you bet me 1 million euros/pounds/dollars that I can't write the same logic in just SQL. I'm allowed to change the database to achieve this.

Anyone want to take the bet?

 

[ESquared]

First, I mentioned several things that can't be done with macros aside from sequential record processing.

Second, "I'm allowed to change the database to achieve this" pretty much blasts any point in betting. You could warp and twist just about any database until it did something one certain way, but that doesn't mean it's useful for anything else.

Anyway, try your hand at this problem: thread183-865740. Then tell me if you want to make the million-something-currency-unit bet.

 

[Ed2020]

BNPMike - Couldn't agree more with your statement about SQL and looping through recordsets.

Recordet loops are used far too often in my opinion. The *vast* majority of the time SQL can do the same job a lot more efficiently.

I've even seen developers looping through a table deleting records one at a time to empty the table!

Ed Metcalfe.

Please do not feed the trolls.....

 

[CajunCenturion]

A basic question: The VBA code is compiled and therefore execute quickly (relatively speaking anyway). Are Macros also compiled, or are they fully interpreted each and every time they are executed?

I am by no means a Macro expert, but I would ask those of you who are how to accomplish the following tasks, using just Macros. If it can be done, then I'd be grateful to know just how.

Get your computer's IP Address? Operating System and Version?

Determine if Internet Access is available and if so, provide FTP services to transfer files to/from remote locations (ie corporate or other branch offices)?

Open up your mailbox, read through the InBox finding those mail items which contain attached spreadsheets that need to be saved and unzippped from the email, and imported into the Database?

Reverse that process, create a spreadsheet, zip it up, and send it out as an attachment to an email?

Establish a connection to a secondary remote Database?

Send a message (such as Minimize Window) to another application?

Place the cursor directly on top of a specific button?

Search an entire drive, all directories and subdirectories, looking for a specific file?

Make/Read a Registry Entry for any number of reasons, including security?

Have five separate but concurrently running timers on the same form?

And most of all, explain why the Built-In Performance Analyzer recommends that Macros be converted to VBA Code?

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 please feel free to modify the database as necessary.

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

 

[jmeadows7]

Hmmmm...gues the answer can lie in consulting:

It depends.

I've developed graphics with c primitives under UNIX back in the mid-80s to doing asp programming in the mid 2000s. It all depends upon what you want to do and what you have to do it with.

An app I wrote to run on a pc back in the early 90's had to be written in C reading several hundred lines of data into C structures built with pointers to perform a binary search. Why? Because the database / pc / hard drive combo was too slow to perform the same task back then. Would I do it the same way in 2004 - probably not. Some of that C code got pretty cryptic with pointers and doubly linked lists.

So, go with what you know, meet your requirements - but document it for later use!!

 

[BNPMike]

ESquared
I've looked at Thread183-865740 and it would be nasty but not impossible to do it with SQL and therefore macros. Initially I thought I would nevertheless demonstrate how you could do it but after a while I thought this is such a stupid problem, it's fatuous to waste time on it. There is no business logic to use the wierd process 'imposed' rather than simple aggregates. The issue is not solving a technical problem but managing users by telling them they are mentally defective - well in a sliglty less direct way.

Cajun
These days it really doesn't matter whether something is compiled or not. The delay is going to be in executing the SQL plan not compiling it. VBA tempts people to use recordset processing which is always slower tham SQL.

Otherwise you are right, there are lots of bells and whistles that need other solutions, but should you be using Access or C#?

 

[ESquared]

BNPMike,

I happen to agree with you about that thread, but nonetheless, it's a valid example of a real-world requirement where recordset-based operations are required.

 

[BNPMike]

Discussions like this are dangerous. I came in this morning feeling quite aggressive. I decided I hated testers, which is odd because they've never done me any harm. Indeed they've never done me any anything, as I can't ever remember needing to use one...

 

[CajunCenturion]

Otherwise you are right, there are lots of bells and whistles that need other solutions, but should you be using Access or C#?

My preference is to use what ever language the client who is paying me wants me to use. Personally, I don't care if it's Access/VBA, VB6, PHP, C++, C# or whatever. The key to good programming is build your toolbox and know how to use the tools at your disposal, not the language that you program in.

Everyone one of the items (except the question about the Performance Analyzer) listed in my previous post can be done, and in fact has been done because it was needed as part of the basic business processing logic required by the application in Access VBA. These are not bells and whistles or some cute add-ons.

These days it really doesn't matter whether something is compiled or not.

I beg to differ. That might be true if we lived in a world where all we did was read and write to the disk, which I'll admit, is the crux of some very simple database applications. But, at least in my world, there is a lot of data processing going on where Disk I/O is not an issue. CPU cycles determine how quickly these calculations take place.

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

 

[willir]

I guess another issue is LAN traffic. Minimizing LAN traffic to improve performance, especially on large databases, should be an objective.

Which do you guys think is best for running stored procedures and pass through queries - VBA or macros.

(...and BTW, I have seen macros used to connect to a database via ODBC)

 

[ESquared]

For single tasks it hardly matters which method one uses, macro or code (assuming the task can be done in a macro).

This means that in many cases a macro is more efficient... cleaner.

The point is that when complexity enters the scene, a macro by its nature becomes unwieldy because it has the wrong focus.

Just this week in my job I have a situation where a task was performed by a macro, but I will be converting it to code.

A series of reports was run and each one was then mailed in .snp format. A straightforward and simple method, much simpler than using code (you can't get much simpler than fill in the blanks).

But my new design is to reuse the same report for each mailing, with parameters determining its content and layout. I will use code to accomplish this, and while it is more complicated now in development than simply constructing 8 versions of the report and using a macro, future development will be greatly simplified. The parameters for each report will be in a table. Adding a new report will be as simple as adding a new row to the table. And I don't have to modify 8 different reports the same way when something changes.

Or maybe I don't know the capabilities of macros. Can they execute an SQL Server stored procedure, passing in different parameters read from a table, choosing different grouping levels, changing text, and hiding or unhiding sections in the report? And if they can, will those macros be all in one place? When one macro calls another can I jump to it with a right-click and select? You get the idea.

 

[BNPMike]

I guess I need to adjust my position here.

If it is something simple that you could feasibly do with a macro then why not use a macro. When you start getting complicated (any kind of looping or non-trivial re-use), you have no option.

I'll volunteer a new solution - create macros then save them as modules. This way you don't have to know so much about VBA but you get the flexibility and error trapping.

Access - everyone's friend.

 

[ESquared]

Actually, BNPMike, that's what I did... converted a Macro to VB to learn what I needed to know.

I strongly support using Wizards and conversion tools to get you where you want to go. But I see them as merely a step along the way—I wouldn't convert an entire macro. Instead, I would use the conversion just to teach me the corresponding VB function and its syntax.

 

[CajunCenturion]

BNPMike - Want a challenge just for fun? See if you can rewrite thread705-886076 with simply macros and SQL. If you can pull that off, I will be greatly impressed.

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

 

[BNPMike]

I'm not wading through all that code. Tell me what the problem is in a sentence or two of English. I would guess it is taking a league of teams and generating a random fixtures list for a season?

Macros/SQL get noticeably more feasible if you have a known number of things (ie fixed number of iterations). This might be such a case.

 

[LittleSmudge]

I must admit that I can remember doing what ESquared is talking about, running Wizards to see what they do and learning from the results etc.

And I still use wizards to 'quickly' do some of the boiler plate stuff.

But I also take CajunCenturion's caveats very much to heart.
I'm going to have to stand by my code - so I'm going to make very sure I understand what each line of wizard generate code does and why; and I go in and edit it when I'm not satisfied with the results.

I'm quite comfortable now with most of the wizards
I know which ones I trust
I know which ones I use and then have standard edits to do on the results
I know which ones I avoid altogether


I word of warning for anyone still in the 'learning by looking at the results' phase.

Just because the wizard does it a certain way does not mean it is 'best practice'.
It is A way of doing it.
It is a way that easily fits with the flexibility that a general purpose wizards needs
It is a way that was standard practice in 1992, 1994, 1996, 1999 ( Pick year for introduction of particular feature ).
It is a way that a hard pressed MS softy thought up and got through Peer review when the App was being developed. That doesn't guarantee that it is ideal for your implementation.

Given all those caveats ( and I'm sure there are more )
(Do I feel an FAQ coming on here ? ) go use and check and learn, but don't use in 'blind faith'.




G.

 

[CajunCenturion]

The problem is to take a list of teams, and build a Round Robin scheduler where the teams are inserted into the scheduler in a random order.

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

 

[BNPMike]

Cajun

I have to say I'm slightly awe-struck talking to someone with two stars. If you were in the US Army you'd probably have a private jet.

I'm assuming if you have a list of teams, then there's going to be a situation each week (/month/slot) where each team will want to be matched against another team. Can I assume that every team plays once every slot? Then I assume that each team can only play once at home and once away to any given other team (and never play itself).

So the constraints are
any team plays x matches where x is ((number of teams) - 1) at home then exactly the same pattern away.
every team plays every slot.

Is that about it?

 

[CajunCenturion]

I don't pay much attention to stars, although I would love to have my own private jet (and the funds to operate it), but if I can only have one, I'll take the funds.

As far as I know, home and away are not part of the problem. You have a list of teams, which you select in random order, and devise a schedule that each team plays every other team exactly once. On play date one indicate who plays whom, on play date two indicate who plays whom, and so forth, until everyone has played everyone else, exactly once. The number of play dates will be the number of teams - 1.

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