Question Auditing Programmatic Changes - Help!

[scotiwis]

We have a series of very large Access db's (using linked tables from SQL).
We need to be able to audit/track all changes made to forms/reports/VBA (and ideally queries) by our programmers.
Data auditing is fine - have programmed that into it.
But how on earth do you audit programmatic changes.
Any clues from anyone would be greatly appreciated.

Many thanks

 

[KenHigg]

Are the programmers working off a task or change request list?

 

[scotiwis]

The requests come in via a helpdesk, but the requirement is that ALL programmatic changes are tracked and auditable.

 

[spikepl]

To manage changes in your s/w you need some system for configuration management. Eg. look at MS Visual Source Safe (it has some major limitations, but may be suitable for Access, since Access applications mostly are developed by one person) or Subversion. If you have no clue about this, hire some local help to set you up - both the software and the procedures/workflows.

 

[Lightwave]

How about this?

I have a development version and run time versions and everytime I make a new run time version I have a form which has a version number on it and increment the version number. Plus my old run time front ends are kept so you have a copy of all versions going back.

So I end up with one uptodate development version and multiple historical runtime versions.

If it was very important then I might take daily backups with a vbs script of the development version and so I could roll back to any development version in line with the run time versions. Matching the above up with the manual list as suggested by Ken would for all intensive purposes give you a complete audit trail although granted not textual would probably be more useful.

 

[scotiwis]

Hi guys, thanks for the replies.
I don't really need to be able to roll back or keep previous versions. What I need is for all changes to be tracked. eg a programmer goes in and changes some VBA, or ammends a report, or tweaks a screen. All changes are automatically tracked and output to an audit table. ie nothing can be changed within the databases by the programmers without it being traced - who did it, what was changed.

 

[KenHigg]

I'm not aware of anything in Access or VBA that could be set up to track this automaticaly. Sounds like you have programmers making changes to the production db. If you can't have a dev and prod version, etc and process to manage the db with this set up you have a uphill battle. Do the developers not want to log their changes?

 

[scotiwis]

Yeah the developers can log their changes, but, we're SOX controlled - part of a US company. Therefore the requirement is that all changes need to be logged automatically, ie it needs to be watertight in terms of every change being tracked and audited, not reliant on the developer logging their own changes.

 

[DJkarl]

Yeah the developers can log their changes, but, we're SOX controlled - part of a US company. Therefore the requirement is that all changes need to be logged automatically, ie it needs to be watertight in terms of every change being tracked and audited, not reliant on the developer logging their own changes.

You really need to look at software for version controlling, since each keystroke is a technically a change to the code, there would be hundreds to thousands of changes each time the programmer touched the code. With some of the previously mentioned version / source code management software the original is checked out by individual A, changes made, then checked back in by the same person. You need to keep the previous version to compare what has changed, it seems unrealistic that there would be something to add a comment after every single code change, and potentially useless in the cases where they were cleaning up code to make it more readable.

dim a as string
dim b as string
dim c as string
dim d as string

to

dim a as string, b as string, c as string, d as string

Nothing in the logic of the program has changed, why would you need this change logged?