Track Changes
By Richard Rost
6 years ago
Track Changes to Data - Audit Trail - Log User Activity - Record Edits
Do you want to keep a log of any changes that are made to records in your database? In this video, I'll show you how.
Allison from Tennessee (a Gold Member) asks, "I've got a few different users who work with my database. Is there a way I can track what information they change?
Members
I'll show you how to track changes to just sensitive fields, such as "Customer ID 4 credit limit changed from $5000 to $9000." We'll also build a simple user combo box on the Main Menu so you can track WHICH user made changes to records.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Append Query: https://599cd.com/append
SQL Basics: https://599cd.com/sql
Security Seminar: https://599cd.com/security
OldValue Property: https://599cd.com/oldvalue
Double Double Quotes: https://599cd.com/DoubleDouble
Subscribe to Track Changes
Get notifications when this page is updated
Intro In this video, I will show you how to track changes in your data using Microsoft Access. You will learn how to set up a change log table to record any edits made by users, including saving previous versions of records along with the date and time of each change. We will create a backup table, adjust its structure to store a history of changes, and use a bit of Visual Basic and SQL code to automatically log changes from a form's BeforeUpdate event. This tutorial provides a simple way to keep a record of data updates in your Access database.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to track changes in your data. We are going to create a table to log any changes in your data made by your users and create a history of those changes.
Today's lesson comes from Allison in Tennessee. Allison asks, I have a few different users who work with my database. Is there a way I can track what information they change?
Yes, Allison, I recommend you use something called a change log where we basically set up a table that will track whatever information you want to change. You can track whole records or just individual fields. Let me show you the simplest way to do this.
Let's say we have a simple database here with customers. I want to track a change. Anytime someone changes anything in here, I want to save it, save this entire record, into a backup table. So first, let's create a backup table.
We are going to take our customer table and copy and paste it. Control C, copy, Control V, paste. Let's call this CustomerChangeLogT. We are only going to copy the structure. We do not need all the data that is already in there. It is an empty table.
Now, every time a change is made to the customer table in the form, I want to copy that information and just append the record into this change log. We have to make one change in here. I am going to actually make two changes. Let's go to design view.
First of all, CustomerID is an autonumber. If we want to copy the whole record, that autonumber, we can only have one of these in the change logs. I am going to change that to a normal number. Just a regular number, of type long integer. That way, I can add more than one copy of each CustomerID, for example. You want a whole history.
The next thing I am going to do, I would like to see what date and time it was changed. I am going to add a field in here. Let's insert a row. I am going to put in here ChangeDateTime. It will make that a date/time field. The default value equals now. I have a record of exactly when that record was changed.
That is my CustomerChangeLogT. Let's save that and close it down.
In this form, whenever this record is changed, I want to save a copy of what was there to the customer change log. Unfortunately, this is something you will need a little bit of VB and SQL for. I tried doing it with just a normal append query and it does not work. But it is a real simple solution, follow with me.
Let's go into design view for our customer form. Bring up the properties for the form and find the BeforeUpdate event. Usually, I do a lot with AfterUpdate because AfterUpdate lets you do stuff after you have made your changes and the data has been written to the table. But BeforeUpdate happens when you are done making your changes, before the records are committed to the table. That lets you do things like check the data that was entered and cancel the event if you want to. Or you can do something like this where we can save that information before it is committed to the table.
Click the dot dot dot button over here. You may get a window next asking you what kind of builder you want. Pick the Code Builder. I had that turned off on my system.
Right here, we are in the form BeforeUpdate. We are going to put in a little bit of code. We are going to run an SQL statement. So it is going to be DoCmd.RunSQL. And what is the SQL statement? We are basically creating an append query. We are going to append data into the backup table that we just created, the customer change log.
And yes, I checked to see if we could do this without SQL by just making a regular append query, and you really cannot do it that way.
So here we are going to go: INSERT INTO CustomerChangeLogT SELECT * FROM CustomerT.
And then we need a WHERE condition. So I am going to continue this line down here on the next line: WHERE CustomerID = CustomerID, just like that.
So this is the SQL equivalent of an append query. If you do not know much about append queries, I have a video on that. I will put a link in the description below. Also, if you do not know SQL, I have videos and tons of videos on SQL. It is very handy to learn SQL just to do simple things like this from inside your code. Plus, you do not have to have 10,000 different queries in your database to do lots of little stuff. You can just write one line of code like that.
Let's close that and go back to our database here. Let's close this form. Save changes, yes. Let's open it up. I will change myself here from Ricky to Richard.
Now, if I move off this record or close the form, that event will run. Let's check our table. There it is. There is the old data. It has the date and time in there. It has Ricky, which was the old data. And of course, the new data is Richard and that is stored in the table.
If I come back here and change it next after this and put my middle name in there, now if I move to the next record.
I just got an error and I decided to leave this in the video for you. What happened was it says one record due to key violations. I will hit OK. What is happening is I will bet I know what I forgot to do. Right here, CustomerID. I changed it to a number, but I forgot to take the key off. I forgot to take the primary key off. I should have done that before, I will say no. That will reset this so this is no longer the primary key. Let's turn the key off here too.
This table really does not need a key field because this is just backup stuff. We are not doing anything really with this table. We are not relating it anywhere else. This is just so we have a log of what was changed.
Now that I made that change, I should be able to come in here and put in Dennis and move to the next record. There we go. Good. Now it is saving all my changes.
I forgot a step earlier when we were working on this table. I said to myself, should I go back and re-record that part of the video or just leave it in there? I am going to leave it in there. That way you can see the error too. I make mistakes myself. I have been working with Access for almost 30 years. I still make little goofs like that too, especially when I am recording videos, because I forget to do stuff. It is like when you are shooting hoops and you can make them every time, then you say, hey, watch this and you are trying to show off, and then you mess it up.
When I am recording videos, sometimes I forget steps as I am going along.
Go to a different record here. James Kirk, let's put in Jim. Either move to the next record or close this form or whatever you do. Now the old data, James Kirk, is in there. So this will tell you at least: Customer two was changed on this time and date.
Now, tracking which one of your users made that change is a whole different ballgame because Access does not have, by default, user-level security. It used to in the old versions. It used to be that you could set up a logon and a password and give your users a logon ID. You could actually track that information. But now, in the newer versions of Access, Microsoft got rid of that.
You can add your own back in, and I do cover that in my Access Security Seminar. I show you how to make your own logons and you can track what users made changes. But unless you go through that with your own logon, there is really no easy way to do that. The simplest thing would be on your main menu to put a combo box and have them pick who is using the database and that will just be on an honor system at that point.
But that is how you set up that change log table. You would have to do one of these for each one of your tables that you want to track if you want to save all that information.
Want to learn more? There is an extended cut version of this video available for Silver members and up. In that video, I will show you how to track deletions. So if someone deletes a customer, you will see what was deleted. We will create our own log table and a log function that you can put anywhere in your database. You can track specific fields if you do not want to track an entire table. For example, if they change the customer's credit limit, you can track what the old credit limit was and what the new one was set to. We will track if they access specific areas of your database. So if they click on the contacts button, for example, it will say, hey, access contact info. Then, we will set up real simple user tracking, where on the main menu, we will have the user select who they are for basic logon purposes so we can track that in the database. Yes, it is an honor system, but without going through a lot of work to fully secure the database, this is the best you can do.
How do you become a member? Click on that Join button down below the video. You will see a list of all my perks. Silver members and up get access to all my extended cut TechHelp videos, plus lots of other stuff.
But do not worry, these TechHelp videos are going to keep being free. I am going to keep making them as long as you keep watching them. Make sure you like and share my video if you enjoyed it. Also, make sure you subscribe to my channel and click that little bell to get notified anytime I upload one of these free TechHelp videos.
For more information and links to other lessons just like this one, click on the Show More button below the description. You will see all kinds of other links to different resources.
If you have not yet tried my free three-hour long Access Level 1 class, yes, it is three hours long, go to my website or find it on my YouTube channel. If you like Level 1, then Level 2 is just one dollar and that is also free for members.
Want to see your question answered in a video like this one? Visit my TechHelp page.
Thanks for watching and we will see you next time.Quiz Q1. What is the main purpose of creating a change log table in your Access database?
A. To keep a backup of all the forms
B. To record all changes made to data by users
C. To increase the speed of the database
D. To create new user accounts automatically
Q2. Which method does Richard recommend for tracking changes in customer records?
A. Using Excel to monitor Access changes
B. Setting up user-level security exclusively
C. Creating a backup table and appending changes with SQL
D. Enabling automatic tracking in Access options
Q3. When copying the customer table structure to create the change log table, what should you do regarding the CustomerID field?
A. Leave it as an autonumber and primary key
B. Change it to a regular number and remove the primary key
C. Convert it to a text field
D. Set it as a lookup field
Q4. Why do you add a ChangeDateTime field to the CustomerChangeLogT table?
A. To count how many times a record was changed
B. To record which table was changed
C. To log the date and time when a change occurred
D. To track how long each user is logged in
Q5. Which event on the form should you use to trigger copying old data to the change log?
A. AfterUpdate event
B. OnOpen event
C. OnClick event
D. BeforeUpdate event
Q6. What does the SQL statement "INSERT INTO CustomerChangeLogT SELECT * FROM CustomerT WHERE CustomerID=CustomerID" accomplish?
A. Updates the main customer table with new changes
B. Copies only the current state of all records to the log
C. Appends the original record to the change log table before saving changes
D. Deletes the record from the customer table
Q7. What common error did Richard encounter while testing the change log functionality?
A. Data type mismatch error
B. Key violation error due to the primary key
C. Out of memory error
D. Invalid SQL syntax error
Q8. Why is user-level security not used by default in recent versions of Access?
A. It is too difficult to configure
B. It is available but hidden in settings
C. Microsoft removed user-level security features from new versions
D. It requires third-party add-ons
Q9. What is a simple way suggested to track which user made a change in the absence of user-level security?
A. Using a password for every data change
B. Assigning colors to each user
C. Creating a combo box for users to select their names upon using the database
D. Restricting access to one user at a time
Q10. For more advanced tracking, what does the extended cut of the video promise to cover?
A. Generating automatic reports
B. Tracking record deletions and specific fields
C. Encrypting the change log
D. Sharing logs across multiple databases
Answers: 1-B; 2-C; 3-B; 4-C; 5-D; 6-C; 7-B; 8-C; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers how you can track changes in your Microsoft Access database using a change log. This lesson began with a question from a student who has multiple users working in her database and wants to track when information is altered.
When you want to keep a record of data changes, I recommend setting up a change log. The idea is to create a separate table where each modification gets recorded, letting you track entire records or just specific fields as needed.
In this example, imagine you have a database with customer information. The goal is to save a snapshot of any record that gets updated. To achieve this, start by making a backup table. You do this by copying the customer table's structure, not its data. Name the new table something like CustomerChangeLogT.
Once you have this backup table, you need to modify it slightly. First, make sure the CustomerID field is a standard number (long integer) rather than an autonumber, so the same CustomerID can appear in the log multiple times. Next, add a field, perhaps called ChangeDateTime, to store when each change happened. Set its default value to Now so the exact date and time of the update is logged automatically.
After setting up this table, the next step involves adding some logic to the form where users edit the customer data. Here, you use the form's BeforeUpdate event. This event happens after changes are made but before they are saved to the table, providing the perfect moment to log those changes.
Within the BeforeUpdate event, insert a bit of VBA code that runs an SQL statement. This statement uses an append query to copy the current state of the record from the customer table into the CustomerChangeLogT table, so you have a permanent record of the data before it was modified.
During the demonstration, I ran into a small issue that is worth mentioning. After testing the setup, an error popped up about key violations. This happened because I forgot to remove the primary key setting from the CustomerID field in the backup table. Since this table is just for logging and not for relating to other tables, you do not need a primary key. Removing it resolved the problem, and the change log started recording correctly.
If you want to track exactly which user made a change, that is more complicated. Modern versions of Access do not support user-level security by default as they once did. You would have to implement your own login system, which I cover in my Access Security Seminar. As a workaround, you could have users select their name from a combo box on your main menu, relying on an honor system for identification.
Remember, you would repeat this process for each table you want to track. If you want to be more selective and only log certain fields, that is also possible but takes a bit more setup.
For those interested in taking this further, there is an extended cut version of this lesson available for Silver members and above. In the extended cut, I demonstrate how to track deletions, build a more flexible log table and logging function, record changes to specific fields, and even monitor when certain areas of your database are accessed. You will also see how to create basic user tracking by having users identify themselves when using the database.
You can become a member to access extended cut videos and additional perks, but my regular TechHelp videos will always remain free.
For complete step-by-step instructions on everything mentioned here, you can find the full video tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Creating a backup table for change logging
Copying table structure without data in Access
Modifying a copied table's field types
Removing primary key from a backup table
Adding a ChangeDateTime field with default value Now
Setting up a form's BeforeUpdate event
Writing VBA code to append records before updating
Constructing SQL append queries in VBA
Testing and troubleshooting change log functionality
Understanding issues with primary keys in change log tables
Demonstrating logging of updated record data |
