Need a way to track changes made to an existing table

[Derek]

I am responsible for an already existing database that will have infrequent
updates/changes to the existing records. I would like to be able to keep
track of the dates when these changes occur. Is there anyway to make a field
that will automatically record the date each time a record is updated? Any
suggestions?

 

[John W. Vinson]

I am responsible for an already existing database that will have infrequent
updates/changes to the existing records. I would like to be able to keep
track of the dates when these changes occur. Is there anyway to make a field
that will automatically record the date each time a record is updated? Any
suggestions?

About the only good way to do this in Access is to first ensure that all
updates occur *only* through a Form; if you allow users to get into table or
query datasheets, or to run update queries, you really don't have any usable
events. SQL/Server tables have Update Triggers but Access doesn't really
support them.

On a Form, you can bind a form field to a date/time field (DateChanged let's
call it) and update the field in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<do any validity checking first>
Me!DateChanged = Now
End Sub

 

[Jeff Boyce]

In addition to John's suggestions, you may need to clarify what exactly to
want to record.

If two people change the same record, one yesterday and one today, do you
want TWO changes noted or just the most RECENT change?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Derek]

Thanks for your input. I got the date to update when a record is changed
through the form now. Is there any way to ensure that data can only be
changed through the form without setting up user accounts?

 

[John W. Vinson]

Thanks for your input. I got the date to update when a record is changed
through the form now. Is there any way to ensure that data can only be
changed through the form without setting up user accounts?

Nope. If the user can open the table datasheet you lose all control.

I guess with the loss of workgroup security in A2007 you'll end up with lots
of developers "rolling their own" security (difficult and not totally secure;
workgroup security was apparently dropped because it could not be made really
secure in a file-server architecture such as Access). If you really need data
security, put the backend into a secured SQL/Server.