Track/log changes to live data

[jomuir]

I have created a database that I am happy with, and I am about to receive information to populate it with. I know I am about to be asked to about security and logging of changes made to the data, which I know nothing about!!

As a result before I get into this part of my project I would like to know myself what is and what is not possible!!
Currently I have 2 tables and 3 forms, one of which is a sub form........ All changes will be done via the forms, adding new records, editing existing records or even deleting obsolete records.

•Is it possible to record any changes made to the live data?
o For this to happen I presume that I need to setup users and access rights?
•What type of information can be saved if any?
o What form would this be saved in and where (a temp table?)?

While typing this a change of plan – if it is possible I am going to start building it regardless, as it will enhance my database.

Thanks

 

[RuralGuy]

Search this forum on "Audit Log" or "Audit Trail".

 

[DCrake]

Simple Software Solutions

You could go down the Access Security Route and MDW's but as a newby, so to speak, why not try this.

In your main data table add the following fields

fldCreatedDateTime -Date/Time
fldCreatedComputer - Text
fldCreatedUserName - Text
fldCreatedLoginName - Text

fldUpdatedDateTime - Date/Time
fldUpdatedComputer - Text
fldUpdatedUserName - Text
fldUpdatedLoginName - Text

When records are added to the main table the 'Created' fields are populated to establish which computer the record was added on, Which user was logged into the computer, which user was logged into the application and at what date and time the record was added.

Then in your application at any point the record is updated you could call a function that updates the 'Updated' fields accordingly.

This will then tell you all the details about whena rrecord was first created and subsequently last updated. What it will not tell you is what was updated and the dates of all updates. To do this you would have to create a related table to handle the event log which would record all the changes made to records. In most cases this is not necessary.

The down side to this is if a user deletes a record you loose all the traceability. So it is worth while creating a clone of the main data table to be used as a crypt for all deleted records. DO NOT TELL THE USERS about this. This will give you the traceability for deleted records and allow the opportunity to restore deleted records.

CodeMaster:

 

[jomuir]

Audit!!

I was looking for Change, log, event, record...........but not audit or trail!!

Thanks

I will look at the other post and do a search on audit trail

 

[RuralGuy]

In addition to samples on this site there are pleanty on the internet.
I've used this one for years: http://www.rogersaccesslibrary.com/download3.asp?SampleName=AuditTrail2.mdb
Allen Browne has a sample as well: http://allenbrowne.com/AppAudit.html

 

[The_Doc_Man]

Here's the biggest issue to consider when you really, really, REALLY need auditing. Access is a small-system database. It doesn't have all the niceties sitting in the background like ORACLE and other "big kids" on the block.

To do auditing correctly, you must NEVER let your users see tables or queries directly. You must ALWAYS bring them in through forms. Because.... tables and queries are elementary items that have no sub-structure. You cannot trap anything going on in a table through datasheet view (or equally, a query through datasheet view) because there is nowhere to "anchor" it. Forms, however, have the ability to support infrastructure. You can anchor logging code there.

It is still possible to give users what APPEARS to be a datasheet view of something - because the form wizards can build tabular forms for you. But if you have someone going in directly to tables or queries, that is a "stealth" user who can do anything without a trace.

That's WHY you should surely think about not only securing the database, but limiting your users to what they can see when they get in.

Look up "SWITCHBOARD" forms on this forum and "STARTUP FORMS" in Access and this forum for more on how to control what your users really see.