PLEASE. What is the best way to create a history

  • Thread starter msnews.microsoft.com
  • Start date
M

msnews.microsoft.com

Hi, First let me thank anyone who tries to help me out here.

I have a medium size relational database (4 relational tables) on a network
with about 15 users who enter and modify data. I would like to create a
history table or multiple tables if need be, that tracks what was changed,
by who and when. I have security in effect, so each user has there own
userID and password and, I am able to track entries on userid.

I have considered duplicating entire records when a change is made, but that
sounds like a BAD IDEA.. I can also imagine that there may be more then one
way to do this, so please give me your ideas....

Thanks again.
 
V

Vantastic

I recently had to do the same thing, so I just created a table called
"AccessLog". Then created a normal Form called AccessLog. For each record
that was opened, closed, modified, deleted etc I put in the relevant coding.

Here is a sample from mine:

Private Sub Form_Close()
DoCmd.OpenForm "AccessLog", , , , acFormAdd, acHidden
DoCmd.GoToRecord acDataForm, "AccessLog", acNewRec
Forms![Accesslog]!User.Value = CurrentUser
Forms![Accesslog]!Action.Value = "Closed Training"
DoCmd.GoToRecord acDataForm, "AccessLog", acNewRec
DoCmd.Close acForm, "AccessLog"

End Sub

This opens the form "AccessLog" in a hidden state, goes to a new record in
the table, puts in the currentuser, their actions (sorry, you'll need to
hardcode these unless someone else can help), then it goes to a new record
again (to save) then closes the hiddent form again and the user has no idea
this has happened.

I've found this a pretty good way to do it, although you will notice that
depending on how intense you want to track, your accesslog file may be a
whole lot bigger than any other table in your database. It might be an idea
to flush it out once a month too.

Hope this helps.
 
J

John Vinson

Hi, First let me thank anyone who tries to help me out here.

I have a medium size relational database (4 relational tables) on a network
with about 15 users who enter and modify data. I would like to create a
history table or multiple tables if need be, that tracks what was changed,
by who and when. I have security in effect, so each user has there own
userID and password and, I am able to track entries on userid.

I have considered duplicating entire records when a change is made, but that
sounds like a BAD IDEA.. I can also imagine that there may be more then one
way to do this, so please give me your ideas....
Click to expand...

Well, if you want to track all changes, by whom and when, then yes,
you DO need to record at least *something* every time a record
changes.

What you might want to consider is a log table; in each Form's
AfterUpdate event you could record the date/time, the user's ID, and
(at least) the primary key of the record that was changed. It would be
possible, but a lot more work, to compare each field's OldValue and
Value properties and record which field was changed, and its previous
and current values; this gets snarky because you have to account for
different datatypes.

John W. Vinson[MVP]
 
Top