Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-30-2017, 07:36 AM   #1
markcrobinson
Newly Registered User
 
Join Date: Nov 2017
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
markcrobinson is on a distinguished road
Best use of Macros

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

markcrobinson is offline   Reply With Quote
Old 11-30-2017, 07:45 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,682
Thanks: 97
Thanked 1,279 Times in 1,252 Posts
Minty has a spectacular aura about Minty has a spectacular aura about
Re: Best use of Macros

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.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
markcrobinson (11-30-2017)
Old 11-30-2017, 08:07 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,296
Thanks: 10
Thanked 1,994 Times in 1,955 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Best use of Macros

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.

plog is offline   Reply With Quote
Old 11-30-2017, 09:15 AM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,918
Thanks: 52
Thanked 686 Times in 634 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: Best use of Macros

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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 12-01-2017, 11:25 AM   #5
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 79
Thanks: 0
Thanked 8 Times in 7 Posts
RogerCooper is on a distinguished road
Re: Best use of Macros

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.
RogerCooper is offline   Reply With Quote
Old Yesterday, 02:30 PM   #6
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 726
Thanks: 3
Thanked 172 Times in 164 Posts
static will become famous soon enough static will become famous soon enough
Re: Best use of Macros

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.
static is offline   Reply With Quote
Old Yesterday, 02:59 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,850
Thanks: 8
Thanked 3,643 Times in 3,586 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Best use of Macros

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.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old Yesterday, 03:01 PM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,882
Thanks: 41
Thanked 1,737 Times in 1,688 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Best use of Macros

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old Yesterday, 03:06 PM   #9
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,918
Thanks: 52
Thanked 686 Times in 634 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: Best use of Macros

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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old Yesterday, 04:18 PM   #10
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 726
Thanks: 3
Thanked 172 Times in 164 Posts
static will become famous soon enough static will become famous soon enough
Re: Best use of Macros

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.
static is offline   Reply With Quote
Old Yesterday, 04:29 PM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,882
Thanks: 41
Thanked 1,737 Times in 1,688 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Best use of Macros

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old Yesterday, 04:57 PM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,850
Thanks: 8
Thanked 3,643 Times in 3,586 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Best use of Macros

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old Today, 01:09 AM   #13
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,682
Thanks: 97
Thanked 1,279 Times in 1,252 Posts
Minty has a spectacular aura about Minty has a spectacular aura about
Re: Best use of Macros

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.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old Today, 11:55 AM   #14
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,882
Thanks: 41
Thanked 1,737 Times in 1,688 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Best use of Macros

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.


Code:
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
	<DataMacro Name="macWriteAuditRec">
		<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.


Code:
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
	<DataMacro Event="AfterInsert">
		<Statements>
			<Action Name="RunDataMacro">
				<Argument Name="MacroName">tStudent.macAuditTStudent</Argument>
				<Parameters>
					<Parameter Name="parmActionCode" Value="&quot;CREATE&quot;"/>
					<Parameter Name="parmRecordID" Value="[StudentID]"/>
				</Parameters>
			</Action>
		</Statements>
	</DataMacro>
	<DataMacro Event="AfterUpdate">
		<Statements>
			<Action Name="RunDataMacro">
				<Argument Name="MacroName">tStudent.macAuditTStudent</Argument>
				<Parameters>
					<Parameter Name="parmActionCode" Value="&quot;UPDATE&quot;"/>
					<Parameter Name="parmRecordID" Value="[StudentID]"/>
				</Parameters>
			</Action>
			<Comment>After Update of tStudent</Comment>
		</Statements>
	</DataMacro>
	<DataMacro Event="AfterDelete">
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.

Code:
<Action Name="RunDataMacro">
							<Argument Name="MacroName">tblAuditLog.macWriteAuditRec</Argument>
							<Parameters>
								<Parameter Name="parmTableName" Value="&quot;tStudent&quot;"/>
								<Parameter Name="parmFieldName" Value="&quot;SLastName&quot;"/>
								<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:

Attached Images
File Type: png SampleFromAuditLog.png (33.2 KB, 41 views)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old Today, 12:16 PM   #15
jleach
Newly Registered User
 
Join Date: Jan 2012
Location: New York, NY
Posts: 134
Thanks: 6
Thanked 26 Times in 26 Posts
jleach will become famous soon enough
Re: Best use of Macros

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.

__________________
- Jack D. Leach - Dymeng Services

Last edited by jleach; Today at 12:30 PM.
jleach is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macros or no macros? Update or not? EternalMyrtle Theory and practice of database design 4 06-15-2013 05:25 AM
Macros v VBA LorraineM Modules & VBA 4 08-02-2009 11:31 PM
Access macros that run excel macros pltnsgt Macros 1 04-11-2007 05:59 AM
Concerning some macros AmySciv Macros 1 03-25-2004 11:55 AM
Calling Private Macros N number of times in Public Macros. Xl 97. lambuhere1 Modules & VBA 1 12-03-2001 12:51 PM




All times are GMT -8. The time now is 11:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World