Automating Allen Browne's Audit Trail for a large Database


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:

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      
      ----------            ----------
      audID                  AutoNumber
      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).

Kind Regards,
Mohamed SinghAsked:
Who is Participating?

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

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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You haven't really asked a question, so I'm not sure what sort of help you need. Your checklist seems to be correct, except that you don't run code in the backend, you run it in the frontend (where the Forms are stored and used).
Mohamed SinghAuthor Commented:
Hi Scott McDaniel,

Thank You for responding.

Your observations are noted, I should have been more explicit.

The real challenge here is:
How can steps 1 through 10 be accomplished via vba & not having to do this manually for 120 tables ?

(As an aside, an additional parameter was added here:   The code (solution) will be run in each back-end, thereby eliminating the additional burden of having to run code in the front-end to effect changes to the back-ends).

Kindest Regards,
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can accomplish most of what you want through VBA, but since this is a one-off process, you'll likely spend more time developing and troubleshooting that VBA Code than you would just doing it manually. Even with 120 tables, the entire process would most likely only take a couple of hours at most. The VBA creation process would take far longer.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mohamed SinghAuthor Commented:
Hi Scott McDaniel,

Thank You for the quick response.

Yes, certainly your view is respected.

Performing this (or most of it through code) would be very useful for other projects also.

Currently, i have 3 projects in which to implement this - for the sake of simplicity, reference was made to the largest of the projects in my original question.   I envisage that the best method of implementing (the vba coded solution of)  this would be at the ending stages of a project when the tables are finalised.   In my template forms, i would write the code for the various events for the audit trail & comment them out   | when the audit tables have been created then i would edit & un-comment the events  | test  | deploy.

Kindest Regards,
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Okay  ...

You can loop through your Tables using the TableDefs collection:

Dim dbs As DAO.Database
Set dbs = Currentdb

dim tdf As DAO.Tabledef

For each tdf in dbs.TableDefs
  dbs.execute "SELECT TOP 1 * INTO audTemp_" & tdf.Name & " FROM " & tdf.Name
  dbs.Execute "DELETE * FROM audTemp_"  & tdf.Name
  dbs.Execute "SELECT TOP 1 * INTO aud_" & tdf.Name & " FROM " & tdf.Name
  dbs.Execute "DELETE * FROM aud_"  & tdf.Name

That should take care of everything down to your #6 item(s) (you have two of them), and also 8 and 9

After that, you can use AlterTable statements to add your Fields. Note you should not worry about physical placement of those fields - you just add them, and let Access handle where they land (they'll be at the end). If you want a view of the table that shows fields in a particular order, then create a Query that shows the fields in the desired order.

For example. to add the "audType" field:

dbs.Execute("ALTER TABLE audTemp_Table1 ADD COLUMN audType TEXT (8)")

Here are some examples of DDL language  you can use to create the other ones:

You can also add your other columns to the "aud_" table in the same manner.

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
Mohamed SinghAuthor Commented:
Hi Scott McDaniel,

Thank You - i'll work through this & then give you feedback.

Kindest Regards,
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.