A Simulated Log of Transactions

[JUANCARLOS]

I want to implement a aditional security option for my database MDB Access 2000.
I want to update a table with the fields:


records
Table Id Operation User
Clients 12 Adition (New register) Admin
Clients 8 Delete John Ma
Clients 5 Update Admin
Invioce 15 Update Mary Ho
Invoice 10 Adition Mary Ho

I want to create this table and update (new register) automaticaly when the users add, modify or delete any register of any table in my database, I wnat to know a metod can to this without querys and events in any form.
I think that this module is useful to any programmer.

thanks in advance for your help.

Juan Carlos Herrera Ruiz.

 

[veronica]

Juan Carlos:
Do you speak spanish?? I don´t speak english very well. If you speak spanish tell me because I wan´t speak with you about your project. I program in Access, Visual Basic and SQL, but I don´t understand how do you want to do this without querys and events in any form.

Veronica.
My e-mail is: vzudaire@yahoo.com

 

[MichaelRed]

Juan Carlos,

What you want to do - in concept - is fairly simple. Some of your details may be a more challenging. You will need to use at least one event, BeforeUpdate.

In this event, you need to check the 'dirty' flag of each control to determine if the control value has been changed. If so, record the information you want. In the most common case, this would be:

UserName
ControlName
ControlSource
OldValue
CurrentValue
Date/Time
FormName
FormRecordSource

Of course, if you only want to record certain controls, the code also needs to check that the change (dirty flag) is associated with the specific control.

Also, you need to be aware that - in the general sense - the log table will 'grow' more rapidly than other tables in the database, so you need to have some plan/schema to deal with it (either an archive db to hold "old" changes or a pruning process which limits the size (Number of records) or age (date/Time field) of the log.

There are a few special cases which you probably need to process:

Memo fields should be placed in a seperate table.

You need to check for null values in fields.

Initial creation of a record should (probably) be handled differently from edit.

Others which I do not recall at the moment.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Durkin]

I think what you are looking for are triggers which are like events on a table, capturing additions, updates and deletes. Unfortunately they are not supported in Access, so you will have to use a strategy like MichaelRed is suggesting. Durkin
alandurkin@bigpond.com