Log Changes to Tables
By Richard Rost
2 years ago
Log Data Changes in Microsoft Access with Data Macro
In this Microsoft Access tutorial, I will show you how to log changes to tables at the table level using data macros, ideal for shared databases with custom front ends. Discover how to audit record additions and modifications without VBA, ensuring database integrity even when you can't control user interfaces.
Derek from Grapevine, Texas (a Platinum Member) asks: I have a unique situation in my office where there are 5 of us working on the same Microsoft Access data in a backend database, but we all use our own custom front ends because we all know how to work with Access, and people like to make their own forms and reports and queries and such. That's fine, but for auditing purposes, I need to know when certain records were changed or added. Since I can't put any controls in at a VB level in the forms, is there any way to audit what was done in the tables directly?
Members
There is no extended cut, but here is the database download:
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, data macros, table-level auditing, track changes Access, log data modifications, back-end database, record changes Access, Access auditing without VBA, audit trail in Access, monitor data updates Access, after insert data macro, after update data macro, user-independent auditing Access, track record additions Access, before change data macro, log user activity Access, multi-user database Access
Intro In this video, I will show you how to log changes to a table in Microsoft Access at the table level using data macros, without needing any VBA or form programming. We will cover how to set up a split database with separate back-end and front-end files, and create after update and after insert data macros to record changes directly in a log table. I will explain the benefits and limitations of using data macros for auditing, walk through setting up log records, and discuss what kind of user tracking is possible with Microsoft Access compared to SQL Server.Transcript Today, I'm going to show you how to log changes to a table at the table level using a data macro, so it doesn't involve any VBA or any programming in your forms or any of that stuff. You can do it right in the table. There are some limitations, but sometimes this is the technique that you have to use to do what you want. I'll explain why in just a minute.
Today's question comes from Derek in Grapevine, Texas, one of my Platinum members. Derek says, "I have a unique situation in my office where there are five of us working on the same Microsoft Access data in a back-end database. But we all use our own custom front ends because we all know how to work with Access, and people like to make their own forms and reports and queries and such. That's fine, but for auditing purposes, I need to know when certain records were changed or added. Since I can't put any controls in at the VB level in the forms, is there any way to audit what was done in the tables directly?"
Yes, Derek, if you have no control over their front-end database, they're making their own forms and stuff, you can't lock it down. All you can do at this point is something called a data macro. Now, a data macro is a macro that runs at the table level. So you'll put this in at the back-end table, and you can use it to do things like log when a record was changed, and when a record was added or even deleted. The only downside is a data macro has no idea who did it because the back end doesn't know what user is connected. So, it'll at least tell you what has been changed. You just won't know who did it. But sometimes that's all you need to know, okay, these 10 records were added today, these 15 were modified, good enough.
Let me show you how to do it. First of all, I'm marking this as a developer level. Even though we're not doing any VBA coding, we're going to be doing a little macro writing, but it's kind of beyond the average beginner user, so I'd recommend if you're new to Access, be careful with this stuff because you can mess up your tables. If you're not sure what a split database is and you want to learn more, go watch this video first.
In a nutshell, if you want to share your database with multiple people in your office, you put the back end, which is a database file just containing the tables, on your server or in someone's shared folder. It doesn't have to be an actual server. And then everybody else gets a front end that's linked to it. That's what a split database is. This video explains more.
Alright, so I've created a folder called server on my desktop. And I'm just going to take a copy of my TechHelp database and copy it in here. Alright, there's my database. And I'm going to split this real quick. Just open it up. We're going to go to Database Tools and then Move Data, Access Database. This is the split wizard. It's really simple to run. Hit Split Database. It's going to say, where do you want to put this back-end file? Well, let's put it in the same folder. So it's on my desktop in my server folder. And I'm going to just rename this thing real quick, Back End. Okay. That'll do its magic. There you go. Database successfully split. And now you'll see these are all linked tables. If you hold your mouse over it, it'll show you where the back-end file is.
Now, if you control the user's front end, you can put whatever logging you want in the forms. And you can lock this stuff down so they can't get to the tables directly, they can't make their own forms. And that's usually how it works. Usually, you've got one person in the office who's the Access guy, he's the developer, he makes the changes to the database. But in some certain situations I've worked with, there are other people that want to be able to make their own front end so they can do a custom accounting report or some custom inventory or whatever, but they still have to connect to your tables. Now with an actual database server like SQL Server, you can get information on what user made changes, but with Access, it doesn't track that. It doesn't have any idea who is connecting to this table. So, you could have 10 different front ends on different machines all connecting to the same back end file.
So, option number one is upgrade to SQL Server, but if you don't want to do that, you can put in a data macro so you can at least know what's going on in the tables. You're not going to know who did it, but you'll at least know the date, time, and what was changed.
Quick side note, I do have a different video that shows you how to track changes in the database with VBA, but this, of course, assumes you can control the front end. And if you control the front end, you can also keep track of what users logged on and off. You can create a little table for that too. So, check out these other videos if that is something you're interested in. But we don't have that luxury because everyone's got their own separate front ends.
Let's say this is Rick's front end. And then I'll just make another copy of this. Copy, paste. And we'll say this one is Joe's front end. Whatever.
Alright, these are identical databases right now, but they can have changes in them. But they're both connected to this back-end file. So let's open the back-end up, and let's put a macro in that will track changes to this database. Now, let's say we just want to track the customer T. You'll have to do this in each table that you want to track changes in. And you can create a separate log table if you want to, right, a log ID and what customer record was changed and a description, all that. But I already have a contact table set up. Contacts are basically every time you talk to a customer, you register a contact. If you've seen a lot of my other databases, you know how this works. So, what we could do is use the contact table just to track the changes that are made. So we could put a note in the customer's contacts with the customer ID. We can put in here record updated or changed or whatever, and the contact date time.
So, go to the customer table, go to design view, and then on the table design pane of the ribbon, you'll see this thing called create data macros. There's a bunch of different options in here. There's after insert, update, delete, before delete, before change, create named macro, and there's all kinds of other stuff you can do. We're going to focus today on after update and after insert.
Alright. After insert happens when someone adds a record, after they've added the record. After update occurs after they've changed something. Let's start with after update. Here it is, basic macro builder if you're familiar with macros. If you're not familiar with working with macros, I do cover them a lot in my advanced classes. We'll talk about them at the end of class.
Over here, you got this action catalog, all kinds of different stuff you can use. We don't need it for today. We're going to do something simple. So, after the customer T is updated, in other words, they've made all their changes to all the different fields and they either close the form or move to a different record, that's when the after update event kicks in. It's the same as the form-level after update event. So, we're going to say, what are we going to do? Well, we're going to create a record. These are all the options you have right here. We're going to create a record in what table? We're going to use our log table as the contact table.
Okay, now what are the actions you can do? Well, we're going to use set field. There's not a lot you can do in here. We're going to set a field in this new record that we're creating in the contact table. Okay, now the name of the field in the contact table, let's start with the customer ID. Okay, we're going to update that to what? Well, I want to put in here the customer ID of the customer that was changed. Okay, so in that case, it's going to be CustomerT.CustomerID. You see it comes up in the IntelliSense there. And yes, you have to put CustomerT.CustomerID in here, otherwise, it won't work.
Next, for the description, we'll go set field. This is the same record now. The field in the contact table is description, and we're just going to put in here record updated and now. We'll put the date-time that the record was updated in the description field. And that's it. And that's unfortunately all the information you really can get. You can't get who did it again. Okay?
Alright. So, we'll save that, close it, close the table, save changes, yes. Now, let's open the table up, and I'll just change me to Rick. Okay, now at that moment, after I moved off my record, a contact for me should have been created, let's go to the end, and there it is right there, see? And I guess we really don't need to put the date-time in here because the contact date table automatically puts that in, so we could take that off of there and just put record updated. See how easy that was? See?
Now, it doesn't matter who is doing what from what database. If I come over here and open up Joe's front end, okay, Joe's front end is connected to that table. So, if Joe comes in here and he's got his own custom form, let's say he goes to his customer form. Let's say he goes to William Riker and he changes the phone number, whatever. Okay, as soon as he closes that form, let's go back to the table, take a look in the contact table, come down to the end, right, there it is, there's record five, that's Will Riker was updated. See? So it doesn't matter, you know, what they're using, they can use a query, a table, a form, it's still going to record, it's still going to record the change at the table level.
Let's do one more. Customer T, right-click, design view, data macros, let's do an after insert event. So you want to know when a new record was added. We're going to create a record in the contact table. Alright. Set field. Again, customer ID is going to be CustomerT.CustomerID. Alright. And we're going to set field. Yeah, this sometimes doesn't go away. Note to Access team, this guy floats here even after I've left that field. Sammy, put that on the list.
Alright. Set field, what are we setting? Description. And we'll just put in here record added. Okay, and since this is the after update event it should have an ID at this point because the ID isn't assigned sometimes until after you put the first bit of information in the record. Alright. Save it. Close it. Close it. Save it.
Alright now, if I come back, let's go in a Rick's front-end database now, again it doesn't matter which front-end you use. Did I get it? Did you load? I don't think... oh, there it goes. My system's running really slow, I got to do some optimization. Come on.
Alright, let's say I... let's say I go to my customer table directly. I'm not using a form. I come in here and mess with Tashi Yar... blah blah blah blah blah... right, make some changes, close it. If we go look at the contact table now, come down to the bottom, and there it is, there's the change. Although I did... okay, there's a record updated. I had a brain fart; we're adding a record right now, under the customer table. Come down to the next new one here and add somebody. And now, take a look at the contact table, and there's record added.
Now here's the thing, if you don't want them to be able to come in here and mess with this stuff, okay, all you have to do is create your log table in the back-end file and don't let them link to it. You know, don't tell them about it. Name it something else that they wouldn't be interested in because obviously, they're going to see it. Call it something like Junk104 that they won't care about. That's one of the problems with Access: you don't have any user-level security at the table level.
If you want to learn more about data macros, I do have another TechHelp video. It's actually a fast tips video. It's a quickie where I just show you the before change event and how to update the last updated field in the table. So you want to know when the date the last updated was at the table level, you can do it here too. It's just a little bit more about data macros. But I do cover them in a lot more depth in my Access Advanced Level 6 class. I will include a link to this down below as well if you want to learn more about how these data macros work. And of course, if you have any questions, feel free to post them down below. But that is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Logging changes to a table using data macros
Splitting a database into front end and back end
Back-end database management
After Insert data macro event
After Update data macro event
Creating log records in Access tables
Setting up a contact log table
Writing data macros without VBA
Sharing databases across multiple users
Comparing Access to SQL Server for user tracking
Access table design for data macros
Macro Builder and action catalog in Access
SetField action in data macros
Linking front-end databases to backend tables
Record updating and addition tracking
Impact of design choices on database security
Utilizing a split database in Microsoft Access
Implications of custom front-end development
Access user-level security limitations
Non-VBA options for change tracking in AccessQuiz Q1. What is the primary purpose of creating a data macro in a Microsoft Access database, as described in the video?
A. To create interactive forms and reports
B. To perform data entry tasks automatically
C. To log changes to a table without using VBA or form controls
D. To optimize the performance of the database
Q2. According to the video, who can see and potentially modify the log table if it's linked in the front end?
A. Only users with administrator permissions
B. Only the back-end database manager
C. Anyone who has access to the front-end database
D. No one; the log table is hidden by default
Q3. What kind of macro is used to track changes at the table level in an Access database?
A. Event macro
B. Function macro
C. Data macro
D. Logic macro
Q4. What type of database configuration is being used in the scenario described in the video?
A. A single-user database
B. A multi-user split database
C. An embedded database
D. A cloud-hosted database
Q5. When does an After Update data macro trigger?
A. When a new record is created
B. When an existing record is deleted
C. When an existing record is modified
D. Before any changes are made to the table
Q6. What limitation of a data macro is mentioned in the video?
A. It cannot track the deletion of records.
B. It isn't able to discern who made the changes to a record.
C. It can only log changes made through forms.
D. It has to be manually run by the user after modifications.
Q7. What is suggested as a possible means to secure the log table from users' modifications?
A. Restrict permission to the front-end database
B. Create the log table with a misleading name
C. Encrypt the log table with a password
D. Only logical, not physical, security can be applied
Q8. Which of the following is NOT true about data macros, according to the video?
A. They run at the table level.
B. They require coding in a front-end database.
C. They can be used to track when records were added or changed.
D. They lack the ability to identify which user made changes.
Q9. What can trigger an After Insert data macro to run?
A. Editing a field in an existing record
B. Deleting a record from the table
C. Adding a new record to the table
D. Accessing a record's details
Q10. What does the video suggest you watch if you are not sure about what a split database is?
A. A previous episode of the TechHelp video series
B. A video on the fundamentals of SQL Server
C. A tutorial on how to perform data entry
D. A deep dive on Access form events
Answers: 1-C; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-B; 9-C; 10-A
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 TechHelp tutorial from Access Learning Zone will focus on how to log changes at the table level in Microsoft Access using data macros. This technique does not require any VBA or additional programming in forms, so it is accessible even when you cannot make changes to the front end of your database. While there are some limitations to this method, sometimes it is your best or only option for tracking changes in shared environments.
To set the stage, let's consider the scenario presented by Derek. He and four others share a back-end Access database, but each person uses their own customized front end. Since each user builds their own forms, reports, and queries, there is no centralized control over the user interface. For auditing purposes, Derek wants to know when certain records are changed or added, but he cannot implement any logging at the form or VBA level because he does not control those front ends.
In cases like this, your best option is to work with data macros at the table level. A data macro lets you create logic that fires directly within the table itself, allowing you to track when records are inserted, updated, or deleted. The significant limitation of this approach is that the back-end Access file cannot identify which user made any given change. Access tables are not inherently user-aware. However, you can still find out what was changed and when.
Before diving in, I want to note that this topic is developer-focused. While it does not involve VBA, it goes beyond what most Access newcomers are familiar with. You will be working with macro writing and direct table editing, which can potentially impact your database structure if not handled carefully. If you need a primer on split databases or are unsure about how they work, I recommend reviewing my introductory materials on that topic first.
Here is a brief overview of split databases: When multiple people need to share a database in an office, you place the back-end file (containing only the tables) in a shared folder. Each user gets their own front-end file that links back to these shared tables. This setup increases reliability and allows simultaneous use.
To demonstrate the process, I set up a simple example. I created a new folder to serve as the "server," then copied the database into it. Using the built-in Access Database Splitter, I generated a separate back-end file. Each user's front end then links to this shared back-end file. If you control the front end, you have various options for logging and security, but in cases like Derek's, where users each have their own front ends, your options are limited to table-level actions like data macros.
When your organization cannot upgrade to a more robust platform like SQL Server, where user-level tracking is available, data macros remain a useful solution in Access for basic change tracking, especially for knowing when records were added or updated.
I do have other tutorials that discuss tracking changes to your database using VBA, which works well if you can control front-end code. That approach allows tracking both the nature of changes and the user who made them. In this lesson, though, we focus solely on table-level solutions, given the constraints.
Now, let's work through a practical example. Imagine two users - Rick and Joe - each with their own front-end copy, both connected to the same back-end tables. I decide to track changes in the Customers table. You need to set this up for every table you want to audit. Typically, you'll log information into a designated log table. In this demonstration, I use an existing Contact table to serve as a log where each time a customer record is changed, a new entry is created noting that event. The log record captures the customer ID, a description of the action, and a timestamp.
To do this, open the Customers table in design view. On the Table Design tab, select Create Data Macros. There are various events to choose from, but for simple auditing, focus on After Update and After Insert.
The After Update macro runs every time a record is modified. Within the macro builder, you add logic to create a new record in your log table, setting the customer ID to match the record that was updated, adding a description such as "record updated," and using the current date and time as a timestamp.
After saving and applying this macro, any change saved to a customer record will generate a new log entry. This will occur regardless of which database front end initiates the change, whether that's through a form, table view, or query. If another user makes a change in their own front end, the data macro in the back-end table will still record the update in the log.
Similarly, you can add an After Insert data macro to the Customers table so that a new log entry is created each time a new customer record is added. Again, set this up in the macro builder, capturing the customer ID and using a description like "record added."
Because you cannot use table-level security in Access to fully hide the log table, consider giving it an inconspicuous name so users are less likely to tamper with it. This is one of the inherent security limitations in Access, since there is no robust user-level security at the table level.
For those interested in exploring data macros further, I have a TechHelp fast tips video that covers the Before Change event and demonstrates how to keep a "last updated" field synchronized in a table. I also go into more detail about data macros in my Access Advanced Level 6 class.
If you have questions, please reach out. A full video tutorial with step-by-step walkthroughs for everything discussed here is available on my website at the link below.
Live long and prosper, my friends.Topic List Logging changes to a table using data macros
Splitting a database into front end and back end
Back-end database management
After Insert data macro event
After Update data macro event
Creating log records in Access tables
Setting up a contact log table
Writing data macros without VBA
Sharing databases across multiple users
Creating records in a log table using macros
SetField action in data macros
Access table design for data macros
Linking front-end databases to backend tables
Tracking record updates and additions
Restricting user access to log tables
Handling unique front-end forms with one backend
Implications of custom front-end development
Access user-level security limitations for tables |
