Why all the anti-macro sentiment?

[cabbey77]

I have taken over a couple of databases that are chock full of macros. I have never used them in my own databases as I have always been told that they are the bane of the existence of any Access databases. I took this at face value from trusted sources, but have never known why.

So can someone please tell me what the problems are with using macros?

 

[KornGeek]

Actually, autonumbers are the bane of existence to any Access database.

I personally dislike macros because using code gives me better control, especially when it comes to error handling. Also, (and this may not be true for all people) I can crank out a function in VBA to do the same thing in less time than it takes me to build a macro. This could simply be due to the fact that I'm familiar with the code interface, but not the macro interface.

Macros won't destroy a database (unless you REALLY mess up), but I believe that (nearly) anything that can be done with a macro could be done better via code.

 

[AlexCuse]

I don't like macros because they are a b!tch to maintain. You have to look at your macro in design view, then locate the query, etc...

With code it is all right there for you. Macros are OK if you are unfamiliar with the code, but if you know the code no reason really to use macros.

KornGeek - why are autonumbers the bane of existence? Other than how hard they are to reset I have never had any problems with them.

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[cabbey77]

Hey Alex,

I would go with the fact that autonumbers are only added when a table does not have an apparent primary key. The problem here, is that if the tables are set up correctly, there should always be a primary key, even if it consists of 2 or three fields.

 

[LarrySteele]

Good question,

The main problems with macros is that they're very limited in the functions available. It's inevitable you'll find a requirement that's beyond the scope of what a macro can do, but it's hard to find something that can't be done in VBA. The second problem with macros is that there is no error handling in macros beyond Access's cryptic error messages.

There are certain cases where macros are the only solution. AutoKeys - this traps specified keystrokes and AutoExec - automatically runs when the database is first opened. You can search the help file for more information. Another use I've found for macros is when I'm creating toolbars & menubars to run code. Toolbar and menubar items can't run code, but they can run macros, which in turn can run functions. It's a bit cludgy, but it works.

Hope that answers your question,
Larry

 

[fneily]

Macros must be translated down into code then compiled. It's an extra step for Access so things run "slower". VBA is code which is closer to the machine language. Nowadays computers are so fast, it doesn't matter. If you can write a macro that works for you, so be it.
If you ever use the input mask wizard to format a phone number, you'll see a selection asking to save the special characters or not. Again, in the old days storage was expensive. Now it's super cheap.

 

[KornGeek]

AlexCuse,

Do a search on autonumbers to find some of the problems that people have had. Among the issues I remember (either first- or second-hand):

1) the autonumber pointer spontaneously resetting, causing the database to attempt to create records with a primary key that already exists
2) developers who don't truly understand the purpose and are continually trying to reset the count because one record has been deleted
3) two or more users attempting to add a record at the same time get the same autonumber for their records. This results a collision when the records get added, and usually results in a user losing the data they entered.

These are just off the top of my head. I seem to recall the words "corruption" and "autonumber" being used a lot together. I seldom use them, and when I do, I create my own via MichaelRed's FAQ on the subject.

 

[AlexCuse]

KornGeek - I mostly use autonumbers as primary key for 'temp' tables that end up on SQL server, that could be why I have not had problems. I have a work tracking application (published through citrix, so I don't need to worry about users getting the same autonumber) that does rely on autonumber being the primary key, perhaps I will check out the FAQ you mentioned to see how I can improve that.

Thanks for the heads' up!

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[JoeAtWork]

I've used autonumbers extensively and never had a problem with them. I've had to fix many problems caused by the various quirks in Access over the years, but so far I've never found an autonumber to be the culprit. I've never seen a duplicate number created in a multi-user app.

If some programmers try to use them in the wrong way (i.e. they expect them to always be sequential or expose them to the user as some sort of meaningful information) that does not mean they should not be used at all.