After Update (table level) macro via VBA

The attached sample access file has this macro to audit changes to a data table.

Question: Do you know a way to install a similar macro using VBA as opposed to manually creating the macro shown below? This macro also exists in the attached sample file.macroVBA.pngDataMacroExample-rev1.accdb
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I thought I mentioned this earlier.  There is no other way.  Macros are your only option and there is a reason.  The data macro is like RI in that it is under the control of the database engine which in this case would be ACE.  That way, no mater how the data is accessed, via Access FE, ASP, VB.net, Excel, or whatever, RI is enforced and triggers run because ACE manages the physical data.  That said, you can't use VBA since VBA belongs to "Access" which is the Rapid Application Development tool that we use to create application objects.  Access is separate from Jet/ACE and ACE is the database engine and it does not support VBA.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I also was told you cannot have something like "Hello World!" show up when a table data is updated. Well, I found a solution as you can see at:
http://www.experts-exchange.com/questions/28706287/Table-After-Update-event-macro-to-get-started.html

So, there maybe no documentation for what I am asking for; but there are many experts who look at a problem from different angle and are more solution oriented.

Thanks,

Mike
PatHartmanCommented:
Macros have limited functionality.  If you need to do something that a Macro won't support, you will have to implement it via VBA in your forms.  As long as you understand the weakness in that method - it only affects updates that happen via the form that is running the code, there is nothing inherently wrong with it.  Just know that if you have a second FE or perhaps a VB.Net app that uses the same BE, updates made by them will NOT run the form level code.

Table level macros were created to simulate Triggers in SQL Server.  To do that, they must be run by the database engine (ACE) NOT by Access.  That is the challenge.  ACE does not support VBA.  

If you have a BE that is used by many different FE's and possibly web apps, then ACE is probably not a good choice as the BE.  Upgrading to SQL Server will give you more flexibility with Triggers although there are still limitations but I'll leave that to the SQL Server folks to address.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Robert ShermanOwnerCommented:
@PatHartman, I think the question at hand is whether there is any way to programmatically generate the table level macros.  I was about to see if I could dig up any info on that subject..

@eghtebas, you might want to stow the attitude a bit, since getting a MsgBox to be called is a quite a far cry from discovering there's an entire supporting API to create the tedious table macros.  :D
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
@Robert,

I have a routine that records table names and their field names with default flag ExcludeYN = True. The admin then sets it to false for those fields ought to be audited.

I also have 10 global variables for each of new and old values. I we could, using vba, manipulate these macro generations somehow (directly or indirectly) we will be able to code and prepare login of database (for select fields) with 100 tables in less than two ours. This system will be independent of database and could be used as tool to work with any database.

BTW, 10 global variables means we are limited to audit 10 fields per table (this could be changed). Also, When I say indirectly what I I am considering an out of box solution we may come across to it. There are some other details I will be discussing with you later.

Please continue your work so we could make this happen.

Thanks,

Mike
Robert ShermanOwnerCommented:
Yeah, it's not looking good.   I suspect that the implementation in the database engine is pretty low-level stuff with no "user serviceable" parts inside.   It makes sense and sort of explains why implementing the simplest of Macros here is rather labor intensive.    

Also, it's worth repeating (I think repeating, I believe others have already said here..) that the reason the Data Macros are so isolated from the outside world, and a risk that you would take by calling any sort of VBA code from within, is that the idea is the data engine is supposed to handle these operations, WITH NO GUARANTEE THAT VBA WILL BE THERE to execute functions that you call from within.   So jumping back to even the most basic MsgBox concept... if you were to have that kind of call inside of a Data Macro, and the database file was accessed by a web server, for example, the Data Macro would likely just silently fail since it isn't running inside an instance of the Access application.

I'm pretty sure this horse has left the building.. :D
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Of course I am after a solution not the solution. Thinking to what you mention earlier, we could capture (in a dynamic array "OldValues" of text type) the initial values of all fields on a form in On Current event.

Then set NewValues in after update of the form. In the after update of the same form we run a routine to see what fields are designated to be tracked. From that point on is piece of cake. So basically,  Although we could trigger the execution of this routine at table level similar to the "Hello World!" example, but much easier perhaps just to run it (trigger it from) the forms after update event.

So, we pushed the idea of after update as far as we could.

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.