Tracking changes in Access table

[mbenton]

Hi everyone, I have a "Parts" table which contains part numbers that change frequently, sometimes daily or weekly. I need to track the changes over the next 3 months as well as changes in 2 or 3 other fields i.e., new suppliers, new parts, discontinued parts. Changes are made directly within the table and not through a form. I'm trying to determine the best way to go about doing this to avoid creating a back up database and comparing the changes...I feel there's a better way. Any suggestions? Thanks.

[CJ_London]

Google 'history files' or 'audit files', I think that is what you want

There are two basic ways

- not allow edits to the data, any changes result in a new record being created - so ID, partnumber, PartDescription, Supplier, etc, TimeStamp. Note that a partnumber can have multiple ID's
- create an audit file - tablename, fieldname, value, timestamp which is appended every time a field changes

both can also contain a username if you want to know who made the change

Here is a link to get you started

http://allenbrowne.com/AppAudit.html

[JamesDeckert]

You'll need to create a Form, then you'd have several options depending on your table structure.

You could create a button for changing records which COPIES the original record into an archive table, maybe with an extra comment field which lets the user type a comment about why the change is being make and a date field for when the archiving happened.

Maybe a different button for discontinuing parts if they are to be REMOVED from the table and archived.

[mbenton]

Thanks, the link is good, in fact most options I view lead me to the same link. However, the source table with the fields I want to track is linked to my database and I cannot modify the source table or create a data entry form. Actually, data in the source table is the out put of yet another large relational database. For example, by the time I view the data in my source table it's already modified through another program. I have to work with tables/queries only.

[CJ_London]

If that is the case you will need to take a copy of the table from your core system and on a regular basis do the following

compare core system table with copy table to
a) identify records in core table and not in copy table - these will be new records and need to be appended to bring the copy table up to date
b) identify records in core table and in copy table where field values are different and update copy table
c) identify records in copy table and not in core table - these have been deleted and the copy table will need to be updated

with each of these three actions you can then create an audit log per the post

Depending on the frequency you do this, it may not catch all changes - if you update daily and the core system was changed two or more times since the last update, you will only catch the last one

[mbenton]

Thanks Ajax. solved.