Audit trail of a table in MS Access database(.mdb)
ms-access, ms-access-2007, ms-access-2010
Solution
No, you cannot do what you describe using Access alone.
If you are adamant about keeping the `.mdb` file format then you cannot use Data Macros so the only way you could write to the audit table would be through VBA code behind forms. Any changes made directly to the data table would not be audited.
If you converted the back-end database to an Access 2010 `.accdb` then you could use Data Macros to update the audit table, but the audit table would not be secure. Users would need write access to the audit table so the Data Macro could update it. However, if they can write to the audit table indirectly via the Data Macro they can also write to the audit table directly. A malicious user could circumvent the audit by simply altering the audit table after updating the data table. (And, by logical extension, there is no way to audit changes to the audit table.)
One conceivable workaround would be to keep the data table in Access 2010 and put the audit table in a truly secure location like a SQL Server database. Unfortunately, that won't work because Data Macros cannot update linked tables.
So, in order to have a reliable and meaningful audit feature you will have to move your data tables to a secure back-end like SQL Server (or perhaps MySQL, or any number of other options), set the appropriate permissions on the data and audit tables, and use a mechanism like triggers to maintain the audit table. You could continue to use Access as the front-end interface (via ODBC linked tables to the back-end database) but the security and auditing functions would have to be handled by the back-end, not by Access.
Problem
I have ms access file of mdb format. I want audit trail enabled on one table. For every change I need to modified row details in audit trail table. Here issue is, table doesnt use forms to update/insert/delete and it has composite primary key. We can enable audit trail if we use .accdb format(2010 access-by enabling Data Macros). But I don't want to change existing file format. Is there a way to do this? Please suggest.