multiple queries & macro; design theory

[NetworkTrade]

to solve a need I have 3 queries

first query finds the range of data from date values in an open Form
refering to Table 1

second query contains the first query linked to a Table 2 using an outerjoin
to define which data to find in that table

the third query is a group/sum task of the data contained in the second query

Its logical, each step can be troubleshoot/debugged separately, and it works
fine...I easily sequenced these 3 queries with a macro...triggered when the
user presses a button.

Works great, troubleshoots easy, but have been reading a post with several
MVPers stating that any developer that uses a macro should be fired...

I know there are alot of reasons not to use a macro and I know I could
string these queries together with VBA. But it seems to me a macro is very
simple, organized, and particularly quick/useful for reuse in these
situations. Copying/reusing VBA isn't particularly more elegant.... Since
when has the use of macros become cause to fire a developer...what gives?

 

[Allen Browne]

I think you have 3 stacked queries (i.e. queries that use other quries as
input "tables.") Then you have a macro that contains just one OpenQuery
action, and you use that macro in the On Click property of a command button.

For such a simple operation, a macro would work okay.

For a real database, you would not use that approach because:
a) You do not let the user anywhere near the tables or queries.
You always use forms/reports as the interface.

b) You don't know whether your OpenQuery succeeds (if it is an action
query), or returns any records (if a SELECT.)

c) If something does go wrong (e.g. somebody messed up one of the queries,
or renamed it), the macro fails with unhelpful error messages that you
cannot recover from.

d) If this application were ever to become a runtime, at this point it would
actually appear to crash (i.e. Access closes.)

So, perhaps you have taken the comments of MVPs that apply to real
applications and misapplied them to something that may suit your own
purposes okay.

 

[Dirk Goldgar]

to solve a need I have 3 queries

first query finds the range of data from date values in an open Form
refering to Table 1

second query contains the first query linked to a Table 2 using an
outerjoin
to define which data to find in that table

the third query is a group/sum task of the data contained in the second
query

Its logical, each step can be troubleshoot/debugged separately, and it
works
fine...I easily sequenced these 3 queries with a macro...triggered when
the
user presses a button.

Works great, troubleshoots easy, but have been reading a post with several
MVPers stating that any developer that uses a macro should be fired...

I know there are alot of reasons not to use a macro and I know I could
string these queries together with VBA. But it seems to me a macro is
very
simple, organized, and particularly quick/useful for reuse in these
situations. Copying/reusing VBA isn't particularly more elegant.... Since
when has the use of macros become cause to fire a developer...what gives?

I'm curious as to which post or thread you're referring to. Although I much
prefer VBA code to macros -- except maybe with Access 2007's new enhanced
macro language which levels the playing field somewhat -- I'd say a
statement like that is over the top.

You should be aware, though, of the major benefits of VBA code over macros
(at least in versions before 2007). Pre-2007 macros have no error-handling,
very cumbersome conditional processing at best, and a variety of functions
and features they just can't do.

Reusability has nothing to do with it, because public VBA procedures in
standard modules are just as reusable as macros. The only realy advantage
to macros I can think of, aside from ease of understanding for
non-programmers, is that they can enable the creation of "lightweight"
forms. But the disadvantages heavily outweigh that advantage, from the
point of view of most developers.

BTW, what exactly does your macro do that requires sequencing your three
queries? If the third query is based on the second, and the second is based
on the third, I don't see why you ever need to open any but the third query.

 

[Steve Schapel]

NetworkTrade,

Many developers do not like macros. There are many reasons given for
this dislike. Of the more valid reasons are:
- macros reveal the developer's intellectual property in distributed
applications, whereas the equivalent VBA code is not in cases where the
application is distributed as an MDE.
- macros do not provide for elegant error handling (this is no longer
true in Access 2007).

Most of the other objections you might see are, in my opinion, not valid
and based on prejudice or inexperience.

To say that a developer that uses a macro should be fired is extreme and
unjustified.

As well as the introduction of error handling in Access 2007 macros,
there have also been a number of other enhancements to the macro
paradigm that make them even more attractive. For example, macros can
be embedded to events, variables can be declared, and Access wizards by
default create macros rather than VBA code.

 

[abraham ayala ortega]

hey