Best use of Macros

[markcrobinson]

I would be interested in a discussion as to when it's best to use a macro vs. VB program.

 

[Minty]

VBA generally is more flexible and give you direct control over all aspects of the Access interface - macro's cannot do all of this.

I don't think you can loop through a recordset or create arrays in macro's to the best of my knowledge.

Almost every developer on here would use VBA in preference to a macro, that must also tell you something.

 

[plog]

The only time I use macros is when I use the Task Scheduler in Windows. In it I point to a database along with a command line argument to run a certain Macro. It opens, fires off the macro and then the macro closes everything.

Of course inside the macro it just points to a VBA function that does all the work.

 

[isladogs]

I also never use macros apart from:

a) Autoexec - to run one or more routines at start up before the default form loads
b) Autokeys macro for assigning certain keyboard shortcuts to designated functions (particularly useful during development)
e.g. on 1 app, I use Ctrl+F5 to return to the login form

 

[RogerCooper]

Macros are useful when you are basically conducing a series of action queries and printouts. They are also quicker to write.

I have many macros in my active database, some of which are run daily (by a VBA program), others are run as needed.

 

[static]

Stability maybe?

Do macros need to be compiled?
Do macros ever become corrupt?

It's not much of a selling point anyway.

All other Office applications, although they are called macros are nothing like Access macros, they are VBA. You can record a macro in Excel, Word, Powerpoint, etc, but if you want to change that macro at some point you need to know VBA or just re-record it.

You can't debug an Access macro so a complex macro would be a nightmare to fix if it ever went wonky.

For me, the question is, why do Access macros even exist?

If you use Office a lot learn VBA, because if you can master one you've mastered them all.

 

[pbaldy]

Add me to the list of non-macro users, other than an autoexec macro in automated apps that calls a function to do all the work.

 

[jdraw]

It would seem that one of the most important uses of macros in Access would be those data macros associated with add, update and delete table events. These macros act similarly to triggers. There seems to be little information, discussion and samples of proper techniques.
I do not use regular macros and have only dabbled with table data macros and named data macros.

 

[isladogs]

I've never understood the 'advantages' of using data macros for append / delete / update table events compared to VBA code or running queries.
As a result I never use them.

If anyone here does, I would appreciate an explanation of the benefits

 

[static]

Triggers have their place. Good to have if not overused. Difficult to debug.

The idea of having a layer of intelligence (?) between the database and application is great.

Even though I don't use Access anymore, I saw Access had data macros and got a little bit excited.
After an hour or two of playing around with them, I decided they were a load of crap.
As jdraw said, I couldn't find much info. Maybe I missed something, but they seemed very restricted and not worth bothering with in Access.

 

[jdraw]

Table data macros get triggered when a table modification event occurs --- could be direct table manipulation, or a query, or a form event, or vba. It isn't clear where said macros are stored or executed.
Much cleaner approach to audit trails/logs than form events in most vba solutions.

 

[pbaldy]

I've never used data macros, but I do use triggers now and then. One that comes to mind is a car maintenance app. When they add a new vehicle, a trigger creates entries for the vehicle in a scheduled service table (oil changes, transmission service, etc) and seed records in a services performed table. Those feed "due for service" reports, so the service guys know when vehicles need to be brought in for scheduled service.

Two advantages come to mind. First, there's no way a user can find some obscure way to get around VBA code to accomplish those inserts. Second, the database engine does the work, so the front end is freed up faster, and there's less network traffic. Maybe that's three advantages.

In a sense, it's like making a field required. Sure, you may have code to check before making the insert, but you protect yourself against pesky users by making it required at the table level.

 

[Minty]

Ditto on triggers - I have used them for similar purposes, automated email alerts based on web based inputs to our systems, or setting up default sub records on new main record entries.

I've never looked at them in Access though, as almost all my work is SQL back end based.

 

[jdraw]

Further to named and table data macros in Access.
This relates to an all Access situation. There is a named data macro "macWriteAuditRec".
When it is called with a number of parameters, it writes a record to tblAuditLog. The xml below is part of the named data macro.

<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
	<DataMacro Name="[COLOR="DarkOrchid"]macWriteAuditRec[/COLOR]">
		<Parameters>
			<Parameter Name="parmTableName" Description="name of table where action occurred"/>
			<Parameter Name="parmFieldName" Description="name of field in table where action occurred"/>
			<Parameter Name="parmActionCode" Description="action being logged  CREATE, UPDATE, DELETE"/>
			<Parameter Name="parmOldValue" Description="old value of Field"/>
			<Parameter Name="parmNewValue" Description="new value of Field"/>
			<Parameter Name="parmChangedBy" Description="logon id of user who actioned this change"/>
			<Parameter Name="parmChangedDate" Description="Timestamp of this action Now()"/>
			<Parameter Name="parmRecordID" Description="RecordID of record being actioned (the PK-- this was an experiment)"/>
		</Parameters>
		<Statements>
			<Comment>Named Macro to write  audit log info into tblAuditLog 
13-May-2017 NOTE:: You can copy a named macro via the immediate window using   SaveAsText acTableDataMacro, "yourTableName", "Your directory and Filename and extension"   
        My example: SaveAsText acTableDataMacro, "tblAuditLog", "C:\users\mellon\documents\macWriteAuditRec.txt" and          
 you can print the xml tree formatted using Notepad++ with XML plugin.</Comment>

The following is part of a Table data macro "macAuditTStudent" that relates to table tStudent.

It is listening for any Insert, Update or Delete event on table tStudent.

<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
	<DataMacro Event="[B]AfterInsert[/B]">
		<Statements>
			<Action Name="RunDataMacro">
				<Argument Name="MacroName">tStudent.[COLOR="YellowGreen"]macAuditTStudent[/COLOR]</Argument>
				<Parameters>
					<Parameter Name="parmActionCode" Value=""CREATE""/>
					<Parameter Name="parmRecordID" Value="[StudentID]"/>
				</Parameters>
			</Action>
		</Statements>
	</DataMacro>
	<DataMacro Event="[B]AfterUpdate[/B]">
		<Statements>
			<Action Name="RunDataMacro">
				<Argument Name="MacroName">tStudent.macAuditTStudent</Argument>
				<Parameters>
					<Parameter Name="parmActionCode" Value=""UPDATE""/>
					<Parameter Name="parmRecordID" Value="[StudentID]"/>
				</Parameters>
			</Action>
			<Comment>After Update of tStudent</Comment>
		</Statements>
	</DataMacro>
	<DataMacro Event="[B]AfterDelete[/B]">

When a condition is met, the appropriate table data macro is executed.

For example when the AfterUpdate condition is satisfied, the following RunDataMacro command is actioned.
This includes supplying the parameters to and executing the named data macro macWriteAuditRec.

<Action Name="RunDataMacro">
							<Argument Name="MacroName">tblAuditLog.macWriteAuditRec</Argument>
							<Parameters>
								<Parameter Name="parmTableName" Value=""tStudent""/>
								<Parameter Name="parmFieldName" Value=""SLastName""/>
								<Parameter Name="parmActionCode" Value="[parmActionCode]"/>
								<Parameter Name="parmOldValue" Value="[Old].[SLastName]"/>
								<Parameter Name="parmNewValue" Value="[SLastName]"/>
								<Parameter Name="parmChangedBy" Value="fosusername()"/>
								<Parameter Name="parmChangedDate" Value="Now()"/>
								<Parameter Name="parmRecordID" Value="[parmRecordID]"/>
							</Parameters>

Part of the tblAuditLog output based on actions against the demo database:

 

[jleach]

Triggers in general (be it SQL Server triggers or Access data macros) are really in a class of their own. They're very easy to use for all the wrong purposes, have little few right ways to use them, but for those right ways to use them there's nothing else that can replace them.

Auditing, really, is the only common "right place" to use them, IMO. There's a few other edge case scenarios where they can be helpful (metadata tracking comes to mind), but auditing is where they really shine.

For comparison, go do a search for Allen Browne's pre-data-macro articles on auditing trails in Access. It's a total mess and at best still a half baked solution (not Allen's work, just how we have to go about it). Then take a data macro (which runs on the table level, remember), and you have actual, real audit capability in Access. Whether or not we should be using Access as a BE for something that needs auditing is an entirely different topic.

Now consider how they're most often misused: someone tries to perform application logic with triggers/macros. "When X happens, I need Y to happen also" - which, in the form of data persistence, many inexperience people will say "oh yea, triggers can do this!" The problem is that triggers are extremely difficult to recognize (you have to really dig to even know they're there), extremely difficult to debug (go ahead, try it ), and they don't tend to nest well. That is, triggers triggering triggers triggering triggers is like all of the prior points on acid (not ACID). Things get really ugly really fast - trigger chaining isn't even directly supported in most RDBMSs and even where they are, it's extremely flaky and tends to be more by happenstance than by design... couple that with the extreme difficulty in debugging... might as well shoot yourself. Overuse of triggers is a good way to destroy your database. So: keep application logic in the application (or stored procedures, or anywhere else but triggers!)

With all THAT said, triggers can be used in some highly specialized scenarios (using INSTEAD OF on views to redirect writes to an abstracted table, for example), but really that's at very advanced level: people that can do this correctly (and more importantly perhaps, who know when not to do it) are capable of demanding a very high payscale, let's say.

tl;dr: triggers/macros for auditing? awesome. anything else? probably not.

additional thought: for auditing, temporal tables in newer versions of SQL are phenomenal.

 

[static]

A user would never have direct access to sql server tables, and recording audit logs can be done easily from code (within a transaction) whereas you'd need a separate trigger for each table being audited.

Access users will always have direct access to tables. And since user-level security is no longer an option that makes 'triggers' potentially far more useful.

 

[jleach]

For SQL Server, we have a drop-in stored procedure that handles all the auditing. For every schema update, we run EXEC audit.Reconcile and it goes through the whole works and sets up all the necessary triggers and shadow tables (including updates to existing tables). Took a while to iron out all the bugs in that one, but super easy now that it's done.

The reason audit logging shouldn't be done via code is because any number of applications can be connected to any given database. By putting auditing on the database engine level, we ensure that all changes are tracked, regardless of what tools might have been used to change it (as part of our aforementioned log scripts, we capture the application name and current user from the connection information: each application is given one or more appropriate strings with the information, but even if - somehow - an unauthorized or unknown application makes a connection, we still maintain full a audit of changes - if nothing else, we don't have to remember to include audit code in every application that might be connected to the db).

Again, we don't really bother auditing in Access backends: it's just not worth it. Maybe with data macros, but we tend to find that any data worth being audited requires tighter security than Access as a BE can handle, and thus it tends to live in SQL Server.

Cheers,

 

[static]

Cool. Our applications are all locked up. Nobody else gets at them without our sayso.

I guess this has gone way off topic now. I'm sure OP wasn't talking about triggers/data macros.

But if anybody has a simple working data macro example, I'd be interested in looking at it myself.

 

[jleach]

jdraw has one back in post #14. Data macros are stored as XML: you can open the macro editor, ctrl+A to select all, then paste into notepad++ to see (or vice versa).

I had actually created a DM editor for one of the Wrox programming books using the fact that they're XML based (see attached), and that included a few very basic examples. Here's one:

<ConditionalBlock>
    <If>
        <Condition>[IsInsert]</Condition>
        <Statements>
            <Action Name="SetField">
                <Argument Name="Field">DateCreated</Argument>
                <Argument Name="Value">Now()</Argument>
            </Action>
        </Statements>
    </If>
</ConditionalBlock>

 

[jleach]

Regarding "regular" macros, I wouldn't bother with most myself, and skip right into VBA. Macros are ok for those that don't know VBA, but if you're going to do anything with Access, you'll eventually need to learn some basics of VBA.

The AutoExec macro is of course helpful to call startup code, the AutoKeys macro is ok (but doesn't catch CTRL+ or ALT+ combinations (one or the other, I forget which)).

The only other time I use macros is for when I want to expose some VBA method through a startup switch, in which case I create a macro with the appropriate name an a RunCode command (I think this was previously mentioned in the case of running code via Task Scheduler).