Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-15-2017, 03:37 PM   #16
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 772
Thanks: 4
Thanked 184 Times in 173 Posts
static will become famous soon enough static will become famous soon enough
Re: Best use of Macros

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.

static is offline   Reply With Quote
Old 12-15-2017, 03:50 PM   #17
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 208
Thanks: 13
Thanked 49 Times in 49 Posts
jleach will become famous soon enough
Re: Best use of Macros

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,
__________________
- Jack D. Leach

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

Microsoft MVP 2012-2016

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jleach is offline   Reply With Quote
Old 12-15-2017, 04:00 PM   #18
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 772
Thanks: 4
Thanked 184 Times in 173 Posts
static will become famous soon enough static will become famous soon enough
Re: Best use of Macros

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.

static is offline   Reply With Quote
Old 12-16-2017, 12:50 AM   #19
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 208
Thanks: 13
Thanked 49 Times in 49 Posts
jleach will become famous soon enough
Re: Best use of Macros

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:

Code:
<ConditionalBlock>
    <If>
        <Condition>[IsInsert]</Condition>
        <Statements>
            <Action Name="SetField">
                <Argument Name="Field">DateCreated</Argument>
                <Argument Name="Value">Now()</Argument>
            </Action>
        </Statements>
    </If>
</ConditionalBlock>
Attached Images
File Type: png Screenshot 2017-12-16 03.47.29.png (51.3 KB, 171 views)
__________________
- Jack D. Leach

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

Microsoft MVP 2012-2016

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jleach is offline   Reply With Quote
The Following User Says Thank You to jleach For This Useful Post:
ridders (12-16-2017)
Old 12-16-2017, 12:54 AM   #20
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 208
Thanks: 13
Thanked 49 Times in 49 Posts
jleach will become famous soon enough
Re: Best use of Macros

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).
__________________
- Jack D. Leach

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

Microsoft MVP 2012-2016

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jleach is offline   Reply With Quote
Old 12-16-2017, 01:05 PM   #21
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,192
Thanks: 47
Thanked 1,805 Times in 1,755 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

Here's a sample database (Acc2010) using table data macros. It is part of the dabbling/learning that I did earlier. I've added a few forms for context setting.
This is my only use of macros.

Attached Images
File Type: png DataMacroForAuditLog.png (20.0 KB, 117 views)
Attached Files
File Type: zip demoDataMacro.zip (101.4 KB, 21 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.

Last edited by jdraw; 12-17-2017 at 12:53 PM. Reason: additional info
jdraw is online now   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
static (12-18-2017)
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 09:31 AM.


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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World