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.
DataMacroExample-rev1.accdb
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.
DataMacroExample-rev1.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
@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
@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
ASKER
@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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
https://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