How can Macros be created with Code?

  • Thread starter Thread starter Douglas J. Badin
  • Start date Start date
D

Douglas J. Badin

Can Macros be created with Code? If so, how?

I am using Access 2002 and probably an Access Project (.adp) connected to
SQL Server 2000.

Ok, I have seen this in replies to similar questions,

- "Why do you want to?"
- "If you can write code, why do you need a macro?"

Not that the replies answered the question, I will satisfy your curiosities
if needed.

My client knows Macros. My client does not know VBA and has little time to
learn it and as a result, when I am gone wants to maintain the processes I
put in place with the least amount of effort especially in time critical
situations.

The simplified version of the requirements are:

- Easily maintain the process by the Client
- A configuration table will contain a list of reports that need to be run
depending on how the table is filtered.
- This table is maintained by a Form and will change very little but when it
does the Report Macros are re-generated.
- The client wants to be able review the Report Macro before it is used in
Production
- The client wants to be able to quickly modify the Report Macro when time
is of the essence before it is used in Production without having to try to
figure what went wrong with code that generated the Report Macro.

So, have there been improvements and can Macros be created with Code?

Thanks,

Doug
 
Hi Douglas,

If I learned correctly that you want to create macro objects in Access
using VBA. Unfortunately, it seems there is not an easy way to meet your
requirements in Access. Also, this behavior is not recomended. Maybe you
can create macro in the built-in macro design view in Access database.

I am looking forward to hearing from you soon.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi Michael,

We are currently doing it in Design view and want to automate the process,
hence we are trying to programmatically create the macro.

Thanks,

Doug
 
Doug,

I concur with Michael Shao, that this is not possible the way you have
described it. But can you please give details of the type of macro that
you are trying to "re-generate"?
 
Doug,

So do you mean that the name of the report being printed needs to be
dynamic? I really don't get this idea of "re-generating" a macro. It
really sounds like you are editing the macro arguments, which is
probably not necessary anyway, you could just refer to the selected
report on the form, or whatever... but I'm afraid I still don't really
have enough info to go on, regarding the details of what you are really
trying to do.
 
Hi Steve,

No need to get off on a tangent of whys and the details, I am just trying to
find out if anyone has figured out how to create macros with code.

Thanks anyway,

Doug
 
Doug,

Tangent? You obviously have a different idea of the purpose of these
forums than I do. Ok, if you're not interested in exploring the issue
of appropriate solutions, the answer to your question is yes, creating
macros with code is theoretically possible, but it is a ridiculous idea.
 
Steve,

Calm down!

My idea of these forums is to ask questions and hopefully get answers, not
to be ridiculed.

Asking a question in a forum is not my first resource to try to solve a
problem. In fact it is one of the last stops.

If I wanted to know what other peoples opinions where on how to design
something I would ask that specifically.

I thought my question was pretty straight forward and didn't need to get
side-tracked down a rat hole.

My client asked me a question and in respect I owe the client an answer.

If you have something to offer about your theory I would like to hear it.

Doug
 
Why on earth would you be using macros for an application you're developing
for a client?

You owe it to your customer to use VBA so that you can have proper error
handling, among other things.
 
PMJI.

Man, I would NEVER let a client dink with my code - either macros or VBA. I
know of no way to "create" macros from Visual Basic. There's nothing in the
DAO or VB object modules that I can find to manipulate macro definitions.

What is it they're modifying in the OpenReport? Are they changing the
criteria? Adding/Deleting reports they want to run? It would seem to be a
simple task to create a working table that contains the information that
they can edit via a form. When they click "Go", dynamically perform the
OpenReports driven by the edited table in code.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
The requirements I posted in my first post should answer your question. As
I stated in that post, the client does not know VBA and does not have time
to learn. If something does not work correctly, they want to be able to
easily workaround it themselves with their current knowledge. Not my
requirement, theirs.



I owe it to my client to answer their question no matter how valid you may
think the question is. How many clients did you keep when you didn't answer
their questions? I owe it to them to investigate the feasibility of their
requirements and if it's not cost effective propose alternatives to deliver
a solution on time and within budget.



I see that it's difficult for people to stick to a question and answer it.



It's Ok to say "I don't know". Not everybody knows everything. But to put
a spin on it and not answer the question does not promote trust.



Didn't your teachers in grade school ever tell you that there is no bad
question?
 
Sorry, but I disagree that you owe it to your client to answer their
question no matter what. If they asked you how to drive nails with a
screwdriver, you could certainly give them an answer, but it would be a
grave disservice, since it's the incorrect tool for the job. Instead, find
out what they need, and make sure you meet those needs. I've seen far too
many developers simply replicate existing manual processes, when an
understanding of what the user does with the information they get would have
lead to a far more elegant (and simpler, in some cases) solution.

What most of us concentrate on is the intent of what the code is to do, not
the specific question. Too often we see people heading down the wrong track,
trying to do something using a method that, while it may work, is not the
best approach.

It would be reasonably trivial for you to give them functionality that lists
all of the reports in their application in a combo box or list box, for
instance, lets them select which report(s) to view and then open it/them.

And no, I don't believe that "there is no bad question". The reason so many
people are told that is to encourage them to ask questions. In that context,
a poorly thought out question is better than no question at all. Once you're
past that stage, though, the adage no longer holds.

In any case, you were told twice that it couldn't be done.
 
Hi John,

The client wrote a system of Tables, Macros, Queries, Forms and Reports over
the past 5 years. So in essence, I am dinking with their stuff. I was
assigned a very simple task of seeing if the macro they manually update in
design view to run reports can be created using a table as its source of
data. I was not assigned the task of rewriting the whole system.



At times, a few times a year, they Add & Delete reports to/from the macro.
When they execute a macro, a number of reports are run.



These reports are financially critical. A missing report means missing
revenue.



No one in this small company knows VBA. As a result they are trying to
improve the process without introducing VBA at a critical step in process
where when it breaks, their cash flow stops until a programmer can be
brought in to correct it. Hence they would accept VBA or something else to
create the Macro because if that part broke, they could still manually edit
the macro which is still in the process.



You're working table is what I described in my first post list of
requirements:


-----
- A configuration table will contain a list of reports that need to be run
depending on how the table is filtered.
- This table is maintained by a Form and will change very little but when it
does the Report Macros are re-generated.
- The client wants to be able review the Report Macro before it is used in
Production
- The client wants to be able to quickly modify the Report Macro when time
is of the essence before it is used in Production without having to try to
figure what went wrong with code that generated the Report Macro.
 
My client wrote a system of Tables, Macros, Queries, Forms and Reports over
the past 5 years which is working very well without having to manually write
any VBA code. Access has some nice wizards that will generate VBA for
certain things.



The nice thing about macros is that it allows non-programmers to automate
and batch tasks. In fact the nice thing about Access, and probably a
founding premise, is that it allows novices and non-programmers to create
systems without the need for a programmer.



Your idea of combo or list box would only make the process more complex by
introducing more clicks, code and more possibility for error.



Currently where 1 double-click (2 Clicks) would run a macro that ran 10
reports; your idea would require at least 20 clicks:



1 (Click to Select)

+ 1 (Click to add to list of reports to run)

----

2

*10 (Reports)

----

20

+ 1 (Click to process the list)

----

21



For 20 reports, that is 41 clicks.



Plus, you have now introduced the possibility of error where they could
forget to run a report.



If they know the report to run, they could go the Report Tab and run it.
The only difference with your idea is that you have filtered the list.



Oh, BTW, you analogy about nails and a screwdriver do not fit for this
scenario.

- They know how to drives nails and they know how to use a screwdriver

- They know how to write macros but they don't know how to write VBA code
 
I'm not suggesting that you "rewrite the entire system." If they already
have a source table that defines the reports they want to run, it would seem
a simple matter to create a form UI that lets them select the ones they
want, perform any modification they need (such as to the Where Condition
criteria) and then click a button to run the reports from VBA. The trick is
determining what changes they feel they need to make to their "macros" and
then implement that in code. They could still go back to their old macros
if your code broke. <s>

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
I disagree with your "41 clicks" scenario. Your "improved" system could
keep track of the reports they ran last time in a table - instead of in a
macro that they modify each time. One click to open the form to display the
list of reports with a button to add / remove / modify the list. If the
list is OK, just click Go (2 clicks total) to run the reports without
forgetting any. When they need to modify it, you can provide an editor that
looks like the macro editor but is really editing and verifying the "driver"
table.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
In creating such a system, the Price of Conformance will exceed the Price of
Non-Conformance which is something I can take back to the client and they
will understand, $$$.

Meaning, from a business point of view, with current resources it is not a
good time to create such a system. This solution can be re-visited in the
future when the Price of Conformance does exceed the Price of
Non-Conformance or maybe Microsoft will expose the Macro Object.

Thanks,

Doug
 
If you think about it, you would still have to create some sort of UI *and*
figure out how to write macros for them. And then they would still have to
go to the macro you created to verify and run it.

The bottom line is they need to trust in a code solution that will do away
with their having to edit / run macros. How many errors do they introduce
when they do the macro editing?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top