|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Backup Record Before Edited By Richard Rost 
6 years ago
Backup a Record Before it's Edited In today's lesson I'm going to show you how to backup a record in your database before someone makes changes to it. This way you have a complete history of all of the edits, in addition to a date/time stamp of when it was edited. Topics Covered
MembersThere is no Extended Cut for this video. LinksInput Masks: https://599cd.com/InputMask
IntroIn this video, we'll talk about how to back up a record before it is edited in Microsoft Access. I'll explain why using an extra backup table is more reliable than adding fields to your main table, show you how to build an append query to copy the current record, and set up the On Dirty event in your form with a bit of VBA to automate backups. We'll also discuss best practices, handling warning messages from action queries, and things to consider if your table structure changes.TranscriptWelcome to another TechHelp Q&A by AccessLearningZone.com. I'm your instructor, Richard Rost.Today's question is about backing up the data in your database, and I'm going to show you how to do a quick record backup. In other words, before someone edits a record, we want to keep a backup of that data. We want to keep any change that they make so we can see what has been changed. Today's question comes from Simon. It says, "I'm playing around with action queries in Access 222. And you said before you run an action query you should always back up your data." That is sound advice that came from me, so of course, it's good advice, and I always follow it. But just for the sake of it, I tried something with an action query and it worked. But I was just wondering if you would consider it to be sound practice. In the customer table, I created two more fields called "updated" (yes/no) and "old data" (short text). In the update query, I was changing the company name, so I also made the update put the company name in the old data field and marked the updated field to true. The result was the company name was updated correctly and the old company name was stored into the old data field and the updated field was set to true. It did not matter how many fields were arranged in the query; it would always work the way I wanted to. Can I rely on this result to be constant, or could it be hit or miss sometimes if the old data field gets the old or updated value in the query? Well, the only problem that I see with this is that you're limited to that particular field, and if the user makes a change and then makes another change, then another change right away, you've only got one field in there. This is the whole reason why we want to use relational tables. I would use a second table to store your backup information. Just create a customer backup table, and then every time that record is edited, just drop whatever the record currently is into the backup table. That way, we always have an unlimited number of backups for that customer, and you can see all the changes to all of the fields in that table. I cover something very similar in my Access Security Seminar. I'll give you a link to that at the end of the video, but let me show you real quick how I would do this in just a few minutes. Okay, basic customer database, customer template. Here are my customer fields: first name, last name, address, city, state, and so on, and a basic customer form. Now, assuming that our users can only access the database through this form, and they can't go in and directly type into the customer table, we can use a form event to basically take this information here and drop it into a backup table. So first, let's create the backup table. CustomerT, let's make a backup table. I'm just going to copy and paste this guy. Copy, paste, we'll call this CustomerBackupT. We'll copy the structure only, we don't need the data. Now, a downside with this method is that if you make any changes to the customer table, if you add any new fields, you'll also have to put them in your backup table. Otherwise, the query won't work, so that's one downside to this method. Let's design this guy in design view. The first thing I'm going to do is turn off this guy being a primary key, and I'm going to change autonumber to just a regular number. Let's go with number of type long integer, and that's because when I copy, let's say, Richard Rost is user number 14, I want to be able to copy that user number 14 multiple times into this table. You could set up a second primary key field if you want to in this table, but I really don't see it being necessary. The second thing I'm going to do is come down here and put DateUpdated right down here, and that'll be a date/time, and the default value will be =Now(). That's how I can keep track of when this change was made. If you were following along with my Access Security Seminar and you've got user logon set up, you can actually track in here who made the change (ChangedBy), but we don't have that set up in this simple database. So right now you'll just be able to have the date and time that it was changed. So now we have a receptacle, a place to put our data. Let's save changes here. Yes. Let's make a query, an append query that will copy the current record on the screen into this table. Leave this form open so we've got an idea here to work with. Now, we're going to go to Create, Query Design, Add Tables. Actually, the first thing I'm going to do is change it to an append query that adds records on the end of a table. Append to which table? We're going to append into our CustomerBackupT in the current database. Click OK. Now, add the customer table as a record source. I'm going to close this now. I'm going to add the star, all fields. That means copy all the fields from CustomerT into CustomerBackupT. Again, that's why they've got to match; you can do individual fields if you don't care about all these fields. You don't have to bother with all these. If you only want, you know, customer name and address and you don't care about all the rest of the stuff, then don't, but I'm just going to do all the fields. But I need a criteria in here to know which customer to copy, so that's going to be the customer ID. You can leave this here or not. I'm just going to get rid of it, and then for criteria, it's going to be =Forms!CustomerF!CustomerID. In other words, find the record in the table where the customer ID equals the current customer. If you go to display mode right now, you'll see just that, just Dick Rost, and yeah, it says Dick because I was playing around earlier. We're going to change it in a minute, and you'll see how it works. But if you run this query right now, go ahead and run it. Nothing appears to happen, but if you open up your customer backup table, there it is. The append query ran. If I run it again - got to go to design and then run - it should show up in there a second time. It's going to append each time we make a change to the form. Let's save this guy as MyCustomerBackupQ, the customer backup query. We can close it now. What do we use to trigger it? I want it so that as soon as the user starts typing or changing anything in this record, it automatically runs that append query in the background. We can do it with one line of VB code, or we could use a macro. I'm a VB guy, I like to use VB if possible. I only teach macros for theory. There's really no reason to use macros now. I don't like them. So, right-click design view, let's open up the form's properties. I do this by double-clicking in the little box right there. You should see Property Sheet; make sure it says Form. Now, there's a bunch of different events in here, and I cover all these events in my full classes. You'd think you want something like Before Update or Before Insert. No, the one that we want is On Dirty. Believe it or not, that's the best one for this particular instance. As soon as the user starts making an edit, the record becomes dirty. In other words, the record exists in the computer's memory, but it hasn't been written out to disk yet, and that's when we want to grab it before any changes are made. So it's On Dirty. As soon as the record becomes edited in any way, hit the ellipsis button here. The Visual Basic editor appears. You might see a little window that says, "What builder do you want?" Pick the code builder. Right here, all we have to do is run the query that we made. So: DoCmd.OpenQuery "CustomerBackupQ" and that's it. There might be one other thing we have to do in a second, but we'll talk about that in a minute. But this should work. I'm going to save that, and I like to leave the window open on the side. I just kind of slide it over here. Let's see if it works. Close this. Close this. Open up the customer form. Now, I'm going to change this back to Richard. As soon as I type in that "R," see that pencil that appears right here? That means the record is dirty, and at this moment, that record should exist in my customer backup table. Let's go take a peek. Yeah, there are three of them, see that? Let me finish the edit. Now, if I move to a different record, I'm done editing. That record gets saved in the table. If I change it again - let's change it to Joe - now Richard should have been saved in there. See, there's Richard. Every time you make a change, no matter what it is. Let's say this is now Fort Myers. I'll come back to it and now we'll make this Joe Smith, and now you'll see the previous one with Fort Myers is in there. See that? Every time you make a change, the backup information is saved to the table. Now, why I said there might possibly be something you have to take into consideration: there are a couple of settings that I make in Access to make things easier, my preferences, and I teach these in my full classes. If you go up to File and then come down to Options, under Client Settings, there's an option right here. This is Confirm, and then there's Record Changes, Document Deletions, and Action Queries. Action Queries is normally checked on, and you're going to get a little warning message every time that query runs if this is on. I like to leave it off because I don't care about action query notifications. Just like another one that I always change on a current database is I always change it to overlapping windows instead of tabbed documents. I teach this in my Access 101 class because I hate tabbed documents. But if you want to be like me and be cool, turn off the action queries here if you don't want to. You can circumvent that with a little bit of code. All you have to do is come in here and say: DoCmd.SetWarnings False You don't want the warnings on, but make sure you turn them back on afterwards. In other words, you're going to turn off the warning messages for one command and then turn them back on again if you don't want to make that setting in Access or if you're going to share this with other people and you don't necessarily know if they're going to have that setting changed. Another thing I was a little worried about, but it doesn't seem to be an issue, is if the customer ID was blank, if you're trying to run on a new record, but it doesn't seem to be a problem. But if it was, you could just put in here: If IsNull(Forms!CustomerF!CustomerID) Then Exit Sub but you don't need to worry about that. I just tested it and it seems to work fine, especially if you have the warnings turned off. Again, really, all you need is this one line of code, and if you make that simple little change, that's all you need. So, if you care about more than just a simple track of the backup data, if you really want to care about who did it and you want to make sure that they can't go in and edit your tables and change the backup data itself, if security is an issue, get my Security Seminar. I'll put a link to it in the description below. But if all you want is a simple little backup for yourself, then what I just showed you is fine. If you want to learn about any of the other topics that I talked about in today's TechHelp lesson, the Now function, field types like AutoNumber, and that append queries, I've got an append query lesson available. I'll put a link to that below. Warnings, the On Dirty event, all that stuff, I've got full lessons to cover all this stuff. So I hope you learned something today. Make sure you check out the template section on my website. Be sure to subscribe if you want to see when I release new videos like this, and make sure you ring the bell. Hit the little bell button and pick "all" because then you'll get notifications. You'll get an email every time I post a new video on YouTube. If you want to see your question answered just like this one, you can send it to me on my TechHelp page. That's where you get priority, by the way. Send it there on the TechHelp page. You can email me if you want to, but if you send me a big long email without it going through the TechHelp page, I might not read it right away. I'll read all of them eventually, but sometimes if I see a big long email coming through regular email, I just snooze it until I have time to read it. I've got forums on my website that you can participate in. I've got a pretty active Access Learning Zone Microsoft Access discussion group on Facebook. There's all my other pretty stuff: YouTube, Twitter, my blog. Okay, here's the shameless advertising portion of the class. You watched the free lesson, now I get to advertise to you a little bit. Access Level 1 is three hours long. Watch it if you want to learn the beginning basics of Access, or if you've got a friend that wants to learn Access, point them here. If you want to continue on, Level 2 is just one dollar, and I've got a special membership option available as well where you can get half off by getting a new class every week and all the way up to my developer stuff. That's it. Thanks, we'll see you next time. QuizQ1. Why is it important to back up data before running an action query in Access?A. Action queries can change or delete data, so having a backup allows you to recover previous information if needed B. Action queries require a backup to run C. Action queries will only update backed up records D. Backing up data improves the speed of action queries Q2. What was Simon's method for tracking changes in the customer table? A. He created two fields: "updated" and "old data," to mark changes and store the previous value B. He used a separate backup table for each change C. He exported the table to Excel each time a change was made D. He used macros to lock records before changing them Q3. What is a major limitation of storing backup data in fields like "old data" within the same table? A. Only one backup per field is possible, so earlier change history is lost if multiple changes are made B. It works only for numeric fields C. It increases database security D. It automatically tracks the user who made the change Q4. What is Richard's recommended solution for maintaining multiple backups for each customer record? A. Use a separate backup table where each edit appends the full record, keeping a history of changes B. Add more fields to the existing table C. Email a backup to the user each time a change is made D. Only track changes in a text document outside of Access Q5. When creating a backup table, why does Richard recommend changing the primary key field from AutoNumber to a regular number? A. To allow the same CustomerID to be stored multiple times for multiple backups B. To increase the speed of queries C. Because AutoNumber fields are not compatible with forms D. To ensure the backup table is hidden from users Q6. What additional field does Richard add to the backup table, and why? A. DateUpdated, to record the date and time of each change B. PhoneNumber, to track customer contacts C. Comments, for notes on why backup occurred D. VersionNumber, for version control Q7. What type of query is used to add records to the backup table? A. Append query B. Select query C. Delete query D. Make-table query Q8. How is the correct record identified for backup when running the append query from the customer form? A. By using a criteria that matches the Form's CustomerID to the table's CustomerID B. The first record in the table is always used C. The append query backs up all records at once D. By prompting the user to select a record each time Q9. Which form event does Richard recommend using to trigger the backup process? A. On Dirty B. On Click C. Before Update D. On Load Q10. Why is the "On Dirty" event preferable for triggering the backup? A. It fires as soon as the record is edited, before it is committed to disk, allowing backup of the original data B. It only runs when a new record is created C. It is the default event for all forms D. It prevents users from editing data Q11. What Visual Basic code is used to run the backup query? A. DoCmd.OpenQuery "CustomerBackupQ" B. RunBackup() C. AppendRecord D. ExecuteBackupQuery() Q12. What potential issue is addressed using the commands DoCmd.SetWarnings False and DoCmd.SetWarnings True? A. Suppressing warning messages each time the append query is run B. Logging the user who made the change C. Preventing data from being saved D. Automatically updating form designs Q13. What should you be cautious about if changes are made to the main customer table's structure? A. You also need to update the backup table structure to match, or queries may fail B. Backup queries will update automatically C. No impact, as structures are unrelated D. You must delete all old backup records Q14. What information can the backup table provide if you add appropriate fields and tracking? A. The full change history, including when and optionally who made changes B. Only the current value C. Only the last change D. No useful information for auditing Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-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. SummaryToday's video from Access Learning Zone focuses on how to create quick record backups in your database before any edits are made. I often get questions about backing up data, especially before running action queries, and in this lesson I'll walk you through a practical solution that lets you track all changes made to your records.This topic was prompted by a question from Simon, who experimented with adding "updated" and "old data" fields to his customer table. He set up an update query so that each time the company name changed, the previous name was stored in "old data" and "updated" was set to true. While this basic method does capture the previous value, it has some significant limitations. The primary issue is that you only store one previous value. If a user edits the record multiple times, only the most recent "old" value remains. This does not give you a complete history, and it restricts you to just one field per record. A far more robust solution is to use a separate backup table that can store multiple versions of a record over time. By saving entire records into a customer backup table each time they are edited, you maintain a full history of changes across all fields, rather than just tracking one value. To implement this, start with the basics of your customer database, including your customer form and the fields you want to track. Assume your users are interacting with records via a form, rather than editing tables directly. This allows you to use form events to trigger your backup logic. The first step is to create a backup table. Make a structural copy of your customer table, name it something like CustomerBackupT, and remove the primary key or reset it to a standard number type. This lets you save multiple copies of the same customer in the backup table. Add a DateUpdated field to record when the backup takes place, and set its default value to capture the current date and time. If user security is important, you can add a ChangedBy field to identify who made the changes, but that requires additional user management setup. With your backup table in place, create an append query that copies the current customer record from your form into the backup table. Set the append target to your backup table, and map each field accordingly. Use a criteria expression that matches the current record on your form, so only the active record gets copied over. Once the query is set up, you need to determine when it should run. The best event to use for automatic backups is the form's On Dirty event, which triggers as soon as a user starts modifying a record but before any changes are saved. This ensures you're capturing the state of the record before any edits occur. In the On Dirty event, simply run your backup query using one line of VBA code, invoking the append query you just created. You may also want to address Access settings related to action queries. By default, Access can display a confirmation dialog each time an action query runs, which might be disruptive to users. You can disable these warnings in Access options, or you can use a bit of VBA to suppress them temporarily during your backup operation, making for a cleaner user experience. This method provides a simple and effective way to keep a running backup of every change made to your records. Anytime a user begins editing a customer, the current state of that record is stored in the backup table. You can track each change with date and time, and if you wish, expand to include who made each change as well. If you want added security to prevent users from manipulating backup records or need to track advanced audit information, I recommend checking out my Access Security Seminar for a more in-depth solution. For more detailed lessons on the topics mentioned here, including using the Now function, working with append queries, understanding field types like AutoNumber, handling Access warnings, and using form events like On Dirty, I have full tutorials available on my website. If you have questions you'd like answered in future TechHelp videos, be sure to submit them through my TechHelp page. You are also welcome to engage in the forums or join our Facebook Access discussion group for more community support. And if you're new to Access or know someone who wants to learn, I offer a comprehensive beginner course. There's also a special membership option for ongoing, in-depth Access training. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListCreating a backup table for record historyCopying the customer table structure to a backup table Adjusting field types for backup table compatibility Adding a DateUpdated field with default value Now() Creating an append query to copy customer records Using form criteria in append queries Saving changes in the backup table on record edit Using the On Dirty event in Access forms Triggering append queries with VBA code Suppressing action query warnings with SetWarnings Testing backup functionality in the customer form |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access tips backup ondirty append query techhelp PermaLink Backup Record Before Edited in Microsoft Access |
