I need to implement Allen Browne's Audit Trail in a large database that has more than 120 tables, therefore the need to automate the creation of the new tables as suggested by Allen Browne at: http://allenbrowne.com/AppAudit.html
Restating most of what Allen Browne says with slight modifications for my own consistency, what i need to do via code is loop through all tables &:
1. Make a copy of each table ("Structure Only") not the data.
2. Rename the table with the prefix "audTemp_" followed by the name of the actual table of which this table is a copy.
eg. if the original table is named tblAddress then this copy should be named "audTemp_tblAddress"
3. Remove all Indexes including the primary key, but do not delete the fields.
4. Change the AutoNumber field type to Number, Long Integer.
5. Remove any Validation Rules from the table
6. Remove any Validation Rules from all of its fields.
6. Set the Required Property to No for all fields.
7. Add 3 new fields at the top of the table before other fields:
Field Name Field Type Size
---------- ---------- ----
7.1 audType Text 8
7.2 audDate Date/Time
7.3 audUser Text 255
7.3 audLoggedInUserID Number (Long Integer)
Note: this table has no Primary Key
8. Make a copy of this new table.
9. Rename the table in (8) above as "aud_" followed by the name of the original table.
eg. if the original table is named "tblAddress" then this table should be named "aud_tblAddress".
10. Insert a new field into the table in (9) above:
Field Name Field Type
Set the field audID as the Primary Key.
So, if i originally had 120 tables i will now have 360 tables in total (the 120 original tables are actually in 8 different back-ends, so i'll run the code in each back-end).