Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > Log Changes to Tables < AI Chef Helper 2 | Overflow! >
Back to Log Changes to Tables    Comments List
Transcript Pinned    Upload Images   Link   Email  
Richard Rost             
2 months ago
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.

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 Access

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Log Changes to Tables.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/29/2024 7:35:00 AM. PLT: 1s