Don't Delete Data
By Richard Rost
6 years ago
Don't Delete! Marking Records Inactive.
In this Microsoft Access tutorial, I will teach you when you should and should NOT delete data from your Access databases. We will "soft" delete customer records by marking them "inactive." We'll prevent users from deleting them by setting the Allow Deletions property to NO. I'll show you how to open your customer form to show either Active or Inactive customers. You'll learn how to create a list of only Active customers to select from in a combo box to add a new contact.
Ben from Jamestown PA (a Silver Member) asks: I've got a lot of customers in my database that I know will no longer be buying from me. Some have retired. Some are deceased. I need to keep their records in my table for accounting purposes, but I don't want them cluttering up my reports and combo boxes. How can I remove them without deleting them? Same thing goes with discontinued products.
Members
I'll show you how to archive data to a different table after a customer is marked inactive for 30 days. We will create a Contact Archive table, move the information to that table using an append and delete query, and then create a Union query to see how to put it back together again into one set of data, if we need to.
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
Intro In this video, I will show you why it is important not to delete data from your Microsoft Access database and how to manage inactive records instead. We will talk about the reasons for keeping old data, the risks and consequences of hard deletes, and the benefits of using soft deletes by marking records as inactive. I will walk you through how to add an IsActive field to your tables, update your forms to prevent deletions, and use queries to filter out inactive records from reports and combo boxes, helping you keep your data organized without losing important historical information.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am Richard Rost, your instructor.
In today's video, I'm going to teach you why you should not delete records from your database. Today's question comes from Ben from Jamestown PA, a silver member.
Ben asks, I've got a lot of customers on my database that I know will no longer be buying from me. Some have retired, some are deceased. I need to keep their records in my table for accounting purposes, but I don't want them cluttering up my reports and combo boxes. Can I remove them without deleting them? Same thing goes with discontinued products.
Yes, Ben, I get asked this question quite a lot. You've got a bunch of old products or old customers, and they're just taking up space in your database. For any number of reasons, you might want to get them out of there, but you have to be careful because sometimes if you delete, for example, a product and your products are linked to your orders, now deleting those products might cascade delete your order information, and that's not good.
Let's talk about pros and cons of deleting information from your database and how to handle it properly.
Let's talk about why you would want to delete information from your database in the first place. The first reason people give me is they're getting low on disk space, but hard drives are cheap today. If your two-terabyte drive is filling up, go buy a four-terabyte drive. It's not that expensive, especially if you're running a business.
Second, you may be approaching that two-gigabyte Access limit. Each file, each ACCDB file from Microsoft Access, can only have two gigabytes of information in it, which really isn't that much. However, that's not really a good excuse either because you can split the database and link multiple files together. You can put tables in different database files. I've got videos to show you how to split your database, so that's not a big deal either, unless you've got a single table that is approaching that two-gigabyte limit.
Now a valid reason for wanting to delete information is to keep your database running fast. If you've got big tables with lots of useless information in them that you're not really using all the time, it will slow your database down, especially if you're running over a network. So by getting rid of some of that extraneous information, you can reduce your network traffic.
You'll also increase your indexing speed. If you have indexed fields in your tables, you've got indexed things that you search on a lot, like first name, last name, company name, social security number, that kind of stuff. Every time you add or edit information, Access has to rebuild those indexes, so that will definitely slow your database down as well. Removing information that you don't need can speed things up.
You may be legally required to delete information if someone requests it. I know the EU and some local US jurisdictions have laws that say if a customer wants their information removed, you've got to remove it. But you might not want to remove the customer's information completely because then again, all those orders might disappear. So you have to have a way of handling that.
Of course there's sensitive data, credit card information, medical data, that kind of stuff. How do you deal with that? Credit card info, for example, even if the customer requests that you remove their card information from your active database, you may need to keep it in a backup secure folder because let's say they do a chargeback. The credit card company might say, hey, this customer did a chargeback. Sometimes all they give you is the card number. They don't give you any other information about the customer. So if you can't search your database for that credit card information, you'll have no idea who it is. You may not want to completely delete it, but put it in a backed up sensitive, highly secured folder, so at least a manager can go back and look for it.
These are more business questions that you have to answer for your organization. How do you want to handle this stuff?
Now there are reasons why not to delete information. Number one reason: you need to keep records for historical purposes, accounting, reporting, legal information. If you delete a customer, completely delete the record, and that customer is linked to your order tables, now all your accounting information is off. Especially if you're using cascade deletes, which we'll talk about in a minute.
You might need to go back and look up old information. You discontinue the product that you haven't sold in four years, but a customer who bought it six years ago comes in and says, hey, I need information on this product that I bought, and now you've deleted it, so you've got no idea what it is. If you have a need ever in the future to look up that information, don't delete it.
You may be legally required to keep some information: accounting data, tax information, employee withholdings, regulatory authorities, discovery for lawsuits, if you're part of a lawsuit, all that stuff. If you delete it, like permanently delete it, you could get in trouble, legally.
Stuff that's okay to delete: temporary data you'll never need again. For example, real old emails from inactive customers, you've got stuff from 10 years ago. I have all the emails going back from my customers from when I started this current business, which was 2004. I've got all that history in there, and that's a pretty big table. But I've got it outside of my main Access database, it's a backup file, so I can still go back and get it.
Like Gmail, for example, they save everything. They have tons and tons of space, so they don't care. But if you've got inactive customers that you haven't done business with in a few years, and you know you're never going to hear from them again, then you might want to delete all that old correspondence if it's unnecessary.
Log files, for example, that you've already analyzed. You've got log files from your website from two years ago. Do you still need all of that data? I sometimes hold on to it for about a year, because I can go back and look for where did this customer find me if they become a good customer. But after a certain time, saving all of that information takes a lot of space.
Old email batches, for example, when I send out an email newsletter, my database creates a record for each copy that goes out to each customer. I hang on to it for a little while, but do I really need to save all 30,000 of those records in my sent email folder? No, I don't. I purge it after about a month.
Any data that can be recalculated, you can get rid of. For example, I've had reporting tables that I've built for clients before, where once a month they crunch through the whole month worth of data, and they put together like a balance sheet or a count summary, and that gets stored in a separate summary table. That information, if need be, could be recalculated based on their historical data. So I really don't need to save years and years worth of information in that summary table.
Now, before you delete information, ask yourself, will you ever need to restore this in the future? Am I ever going to have to go back and look this up? If you're absolutely sure it's no, then go ahead and delete it.
Before you delete stuff, though, make sure you back it up. Here is my famous slide that I always put in my videos: back up your data before you do anything relating to deleting stuff. Back it up.
Use cascade deletes very carefully. Now, for those of you who've taken my Access Expert classes, there's something called cascade deletes. That's where you can set it up so that, for example, if you've got an order with line items and those are in two separate tables, if you delete the order, all the line items go away. That sometimes is handy, because if you're sure you want to delete the order, you don't want all those line items sticking around.
But I've seen some people that set up their databases using cascade deletes where they really shouldn't be, for example, customers to orders. They're thinking, well, if I delete the customer, I delete all those orders. But now you've just messed up all your accounting. You have to be very careful when you use cascade deletes.
So the bottom line: when should you delete? It depends. It depends on the situation. That's something you have to answer from a business perspective.
Now, as far as the computer goes, there are two kinds of deletion. There's a hard delete and a soft delete.
A hard delete is where you actually remove the record. You're deleting the record from the table. We do these kinds of deletes to keep our databases small and running fast. That's where you're purging old log files and getting rid of stuff that you no longer need in your primary database.
Then we have what's called a soft delete. A soft delete is where the record stays there, but you just mark it inactive. For example, a customer who's deceased. You know he's not going to buy things anymore. You don't want to lose all of his history just in case, so you mark him inactive. That way he's not cluttering up your reports. When you go to pick a customer from your customer list, for example, he doesn't show up there because you're not going to be placing any new orders. He's no longer on your mailing list, those kinds of things. You want to keep his information, but you don't want him showing up in the rest of the system.
Same thing with employees. You fire an employee. You don't want him showing up in the timesheet table anymore, so you mark him terminated.
So really, as far as the computer is concerned, if you want to work with soft deletes, it's just a matter of changing your terminology. Customers don't get deleted. They become marked inactive. Products aren't deleted. They're marked discontinued. Employees are terminated.
And old bosses, well, they're just set out to pasture. That's where old bosses go. That's a little boss joke.
So whether to delete something or not is a decision you have to make. Think about whether or not you'll ever need this information in the future. Old log files from your website, delete them. Get rid of them. Once you've analyzed it and you've got all the information out of it you want, don't keep that information around unless hard drive space is no concern for you, then sure, dump them into an external file somewhere.
Generally, with your Access database, you're going to soft delete stuff. You're going to mark your customers inactive. So how do we do that? That's what I'm going to show you how to do in the video.
We're going to add an isactive field to our tables. The tables that you care about, we'll just do customers, but you can do products. You can do employees. You can do everything. We'll add a check box to our forms so we can mark them inactive or not. We'll set allow deletions to false. That way our users can't accidentally delete someone. If they try to delete them, they click on the sidebar and hit delete, nothing happens. You have to mark them inactive.
Then we'll use a query to hide inactive records in our forms, lists, and combo boxes. When you open up the customer form, you won't have to see all of these inactive customers.
Then for the members in the extended cut video, I'm going to show you how to archive information. Don't delete it. Archive it. Move it to a different database. We'll add an inactive date field so we know when it was marked inactive. Then after something is inactive for 30 days, we'll have it archived. We'll copy it to a different table in a different database file, so it's not taking up space in your main database. We'll remove that data from the primary tables. We'll copy that record, all that information, to a backup database file.
Now, if necessary, you can still bring those two tables together. If you have to do a report on current customers plus inactive customers, I'll show you how to use a union query. You can union two tables together if the fields are similar. This will be covered in the extended cut.
But for you guys in this video, let me show you how to mark customers inactive and I'll work with that.
Here I am in my simple customer database. This is the free database template. You can download it from my website, by the way. I'll put a link down below. It's real simple. It's got a customer table, first name, last name, email, and so on. In here, I've got a field called isactive. That's a yes/no field, and this just simply indicates whether this customer is active or not. The same field can be found on my customer form right over here, isactive.
Now, any user can still come in here and delete a customer. For example, here's Walter Jones. They can click over here and hit delete on the keyboard, and now Walter Jones is gone. Sometimes you'll get a warning message that says, are you sure you want to delete the customer? I've got my warning messages turned off, but still, we don't want them to be able to do that at all.
To disable the ability to delete records from the customer form, go into design view, go to the form's properties. Under data, find Allow Deletions and say No. We don't want them to be able to delete any records. Now if I come in here and select that and hit delete on my keyboard, nothing happens. That's how you prevent deletion.
If they want to actually physically delete someone, they're going to have to go to you, the developer or the admin or somebody who knows how to open up the table and come in here and delete them from the table. But you shouldn't do that. You should soft delete them and just mark them not active anymore.
How do I see just active or just inactive customers or all of them? We can use a query for that. Create, Query Design, bring in your customer table, bring in the star. That'll bring in all the fields. Then find isactive. Click off the show box so we don't see duplicates, otherwise we'll see two of them because there's already one in here. Now for criteria, just put in here whether you want to see active or not active. I'll put in here true. That'll show me all the active customers. I could save this as Customer Active Q. That will now show me all of the active customers only. Customer Active Q, there's only the active ones.
If you want to make one for your inactive customers, just do the same thing. Copy and paste. I'll copy this query here, paste it. We'll make this Customer Inactive Q. Then I'll edit this guy, Design View, change this to false, save that, and now I can open that up and there's my inactive customers. Of course, if you want to see all of them, just open up the customer table.
When you open up your customer form, you're going to still see all of them, active or inactive, and that's OK. But nine times out of ten, you may only want to browse through your active customers. You don't want to see all the inactive ones. What we could do is put a little checkbox here, and that checkbox will say Show Active Customers. If we uncheck it, it will show the inactive customers.
Go to design mode here on the main menu form. Right click on this button, go to Build Event, the code builder opens up. This is the one line of code that opens up that customer form. It's DoCmd.OpenForm "CustomerF". It's real simple. People who are afraid of programming, don't be afraid of programming. It's very easy.
Come over here to the form. Add one little checkbox on here. Go up to the control box, find the checkbox, drop it right there. We're going to call this guy ShowActive. Let's make that black so we can actually read it. Change the name of that checkbox to ShowActive like that. We'll make the default value under data set to yes. That way, normally we'll see just the active.
Now I've got a value here on this form that I can use in my code. After DoCmd.OpenForm "CustomerF" go comma, comma, comma. That brings us to the where condition. The where condition allows us to specify rules for opening up this form. I can say in here IsActive equals " & ShowActive. What does that do? It sends a rule, a criteria, a where condition, whatever you want to call it, to the OpenForm command. It says, open up the customer form where the IsActive field on that form is equal to ShowActive, which is that checkbox.
So if this is checked on, it opens up the customers where IsActive is true. If I check that off, it opens up the customers where IsActive equals false. Save that. Come back over here. I like to close it and reopen it. Now click the customer button. Now I can see one of three and it's filtered. There's only my active customers there. You can uncheck that filter box to show all of them if you want to see everybody. This is just an easier way to do it. Check the box off, click. Now I'm seeing only my inactive customers. There's three of those as well. You can still mark them active or inactive.
Now if I go ShowActive, I should see four of them. Come on, update. One, two, three, four. I'm going to go to the last record for the update. See that? Watch what it did. It's a little bug. See, it doesn't update. So you go to the last, oh, there it came in that time. Sometimes this takes forever to fill in. It's not you, it's Access.
Now let's say we have a related second table like Contacts. I want to keep track of contacts every time I contact my customer. I want to keep a log of that. Same thing with emails and correspondence and all that, so we'll put that in a different table.
Let's create Table Design. If you don't know about table relationships, go watch my Relationships video. I'll put a link down below in the description. Go watch that first if you've never related two tables together. The Contacts table is going to be related to the Customers table. We'll have our primary key here, which is ContactID, an AutoNumber. The CustomerID will be our foreign key; it's a Long Integer. That's going to store the CustomerID in it. Then we'll have the ContactDate, which will be a Date/Time field. We'll default that = Now. That way it puts the current date and time in there, we don't have to do anything. Then whatever our notes were for the contact, that'll be Long Text. We can store as much as we want in there. Save that as the ContactT table.
If I open that table up, it's pretty straightforward. You'll put in here the CustomerID. Let's say I call, and if you look at your customer table, there's one, two, three, four, and so on. One is Richard, that's me. Those numbers go in here. So I call up one and then whatever the notes were at this date and time. Talked about a new computer, for example. Then a little while later, customer two calls up and says, talked about the weather or whatever. Then I call back customer one, wants to buy. So you have a history of all of your contacts with each of these customers.
Now let's make a form based on this information, real quick form, nothing fancy. So Create, Form Design. Set the record source property for this form, we'll make that the ContactT table. Close that up. This doesn't have to be that big. Let's make it smaller. I cover all of this in my other tutorials if you've ever built forms before. I'm going to give it a splash of color. Let's go with a light yellow like that. Let's save it real quick. Save as ContactF, my contact form. Let's put some data in it: Design, Add Existing Fields. Let's drop all four of these fields right there. Slide them over to the left. Let's align the text all to the left like that. There you go. Shrink this up a little bit. Let's see here, the ContactDate's going to be big, so we'll bring that across like that because it's got a date and time in it.
CustomerID: let's replace CustomerID with a combo box so I can pick the customer instead of seeing CustomerID in there. If we take a look at it now, it's going to look like this. I don't want to see that CustomerID there; I want to put a combo box here so I can pick the customer from a list. If you've never made a relational combo box before, that's where the combo box gets its data from a different table. I have videos on that too for relational combo boxes down in the links below in the description. So let's get rid of this guy. Delete. Let's find combo boxes up here in the control box, it's right there, drop it there.
If you've never made a relational combo box before, I've got videos for that. Go watch that. Look for the link down below in the description below the video.
I want this combo box to get the values from another table or query. Next. Which table or query provides the values for the combo box? We're getting our list of values from the Customer table. Next. Bring over whatever fields you want to see in the combo box. We need the ID, that's got to go first, then just First Name is fine. Next. How do you want to sort the list? Let's sort by First Name. Next. That's what it's going to look like. The key column is hidden; we don't need to see that in the box itself. It'll be there, but it's hidden. Next.
We're going to store that value in the CustomerID in the Contact table. We're picking a customer and we're storing that CustomerID in the Contact table. Next. What label do you want? Customer is fine. Finish. There's my combo box right there. Slide that across like that, so it's a little bigger. Fix your labels up a little bit. Click on that, click there. Maybe space that out.
ContactID: I like to make the auto numbers gray, that way the users know they can't change them. Let's save that, close it, and pop it back open again. There we go. There's my contact form. Richard, James, and Richard again. Let's close that.
Now if you're browsing through your customers and you want to see just the contacts for this customer, here's how you do it. Let's drop a button on this form. Grab a button, drop it up here. We could use the wizard for this, but let me show you how to do it with just straight VB code. It's only one line of code. Put on here Contacts, and that will show the contacts for this customer.
Right click, Build Event, pick Code Builder if it asks you. In here, just say this: DoCmd.OpenForm "ContactF", comma, comma, comma. We don't need to worry about those optional parameters. Here's the where condition. We're going to say where CustomerID = " & CustomerID. That says the CustomerID on the Contact form, the form we're opening, is equal to the CustomerID on the current form.
If you want to, you might see it like that: Me.CustomerID. You don't need that, that's not necessary. You might also see forms like this: Forms!CustomerF!CustomerID. That's if you're using a different form to get the value. We don't need that here either, because CustomerID is on the form we're on. That's all you need right there, one line of code to open up the contact form and show the contacts for this customer. This gets replaced with whatever the CustomerID is. So if it's customer 1, this turns out to be CustomerID = 1, and it sends that. That's why we use concatenation there. I have videos covering string concatenation too if you don't know what that is. I'll put a link down below.
Now that we've got that in place, watch this. I can close this, save changes, open up a customer, hit Contacts, and now I see just the contacts for this customer. If I go browse through them, see just me. It's filtered. You can unfilter if you want to and show everybody, but by default, it opens up just the customer that you're on. Let's go there, see now I see just James.
If you're adding a new record, you might want this to default to whatever is open back here. You can do this. Watch this. Open this up, go to the Data tab. It's Combo8 first. Let's change the name of this combo box, let's call this CustomerCombo. Let's set the data, let's set the default value = Forms!CustomerF!CustomerID. That's how you default a new value to the value on a different form. If I open up Contacts now and go to a new record, you can see that: James, and we're in a new default record.
That's all cool and handy dandy. But what does it have to do with dealing with inactive customers? Let's say you've got an Add Contact button right here on your main menu. If a customer calls in, you don't have to go searching for their record first and then hit Add Customer. You can go right from the main menu. You can just click a button here that says Add New Contact.
Design View, copy this button, copy, paste. Slide it down underneath here, right there. Put right here Add New Contact. Now, what I'm going to put down below it is a combo box right here where I can put the customer right in that combo box. Where are we going to get our list of active customers from? Well, this active list right here.
So let's find a combo box again, drop it down here. Look up the values from a table or query, look up customers, but we're not looking up a customer table. We're going to go to our queries and pick the Customer Active Q, just our active customers. Next. What fields you want? Same things. Sort by First Name, that's fine. Next.
Now, since it's based on a query, it doesn't automatically hide the key column. But just take this column and make that width 0. Next. Which is the bound column? In other words, which column has the data that we care about in it? We really care about that CustomerID, so hit Next. What label would you like? We're going to delete it anyway, so just hit Finish. Get rid of this. Right here is our combo box with our list of active customers in it.
We're going to open up a new contact and put that value in there. Let's make sure it works first. Open up the main menu. Drop this down. I'm only seeing my active customers. The inactive ones aren't in there. Let's take a look real quick and see who are the inactive ones. I'm not seeing these guys on the bottom down here. Yeah, those Lauren and Bill should not be showing up on my list of active customers.
Now, if I go to Add New Contact, we've got to put something in that button there. Design View, right click, Build Event. We're in our code builder again. DoCmd.OpenForm "CustomerF", comma, comma, where CustomerID = CustomerCombo. Did we name the combo box? I don't think we named it yet. Let's go name that first. Leave this code where it is. Let's open this guy up and make sure we gave it a good name. Forgot to do that first. Oh yeah, see, it's Combo20. Don't forget that. Let's give this guy a good name: CustomerCombo.
Go back into our code here, right click, Build Event: where CustomerID = CustomerCombo.
Now, since I'm opening up and adding a new contact, let's do this. DoCmd.OpenForm "ContactF", comma, comma, comma, just like before. Same thing, CustomerID = CustomerCombo. Now do this too: comma, go acFormAdd. It puts you right in the add mode. You're not browsing all the old ones, you're adding a new one.
Basically, this one button, you're going to pick a customer first. This one button is going to open up the customer form to this customer, open up the contact form to this customer, and then go into add mode. Save that. Let's see what we got.
Main Menu. Pick a customer, we'll pick James. Add New Contact. It opened up James back here and it opened up into a contact form and we're on a blank new record and we can add new stuff. The bottom line here is this now has my list of active customers in it, and I can pick a customer down here in it, and I don't have to go through the big, gigantic, long list of all my inactive customers. This is just one technique. This is one little thing you can do.
Here I've showed you how to mark customers active, only show the active or inactive ones in your forms, only show the active ones in your combo box or inactive ones if you want to, and then open up different forms, and this works with reports too, based on that status. There's all kinds of stuff you can do.
The bottom line is you don't have to physically delete those records in your database unless you're trying to save space.
Want to learn more about handling inactive records in your database? In addition to just marking people inactive or their contacts inactive or whatever, I'm going to show you how to actually physically move that information to a different table, and that table could be in a different database file that's linked. So you don't have to worry about it cluttering up your main database.
We've already got the IsActive field to mark whether they're active or not. We're going to add an InactiveDate field, so when you mark them inactive, it will note what date that is. After 30 days, what we'll do is we'll archive all of their contacts. We're going to keep the customer in the customer table and just leave them marked inactive, because customer information is important, and it's usually not that much data.
What we'll do then is create a contact archive to take all his contacts, because we're not going to look them up or talk to them anymore if he's gone. We're going to put them all in a different table. We'll create a ContactArchiveT. We'll create an append query to append all of the contacts from customers who have been inactive for 30 days or more. Then we'll run a delete query to delete that same set of data. So all that information is going to go into the archive table out of your main database. That table can be linked so it can be an external database file, so it's not cluttering up your main database.
I'll show you how to create a union query so that just in case you want to see all that information together, a union query takes two tables that have similar data and puts them all together so it looks like one big long query of information. That's a union query, and that's all covered in the extended cut for members.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. Why should you generally avoid deleting records from your database?
A. Because it can lead to loss of important historical, accounting, or legal data
B. Because Access does not allow deleting records
C. Because deleting records slows down your database
D. Because it increases your disk space usage
Q2. What is a "soft delete" in a database?
A. Marking a record as inactive rather than physically deleting it
B. Deleting a record, but keeping its backup only
C. Archiving the entire database
D. Deleting only part of a record
Q3. What is the main risk of deleting a product that is linked to orders in a database?
A. It may cause cascade deletions that remove orders
B. It can make the forms slower to load
C. It creates duplicate records
D. It increases the Access file size
Q4. Which of the following is NOT a good reason for deleting records from your database?
A. To comply with legal requirements for data removal
B. To reduce the amount of data that must be indexed for performance
C. To permanently remove critical accounting data after one year
D. To remove temporary or recalculable data that is no longer needed
Q5. What should you always do before deleting any records from your database?
A. Back up your data
B. Upgrade your hardware
C. Increase your Access file size limit
D. Disconnect from the network
Q6. What is the function of the "isactive" field discussed in the video?
A. It is a Yes/No field used to mark whether a record is active or inactive
B. It stores the last order date of a customer
C. It indicates if the customer has an email address
D. It tracks the number of purchases made by the customer
Q7. When might you be legally required to keep data, even if you do not use it anymore?
A. For accounting, tax, or regulatory purposes
B. For daily reporting needs
C. To ensure customers never become inactive
D. For increasing query speed
Q8. What technique did Richard demonstrate to ensure users could not delete records from a form?
A. By setting the form's Allow Deletions property to No
B. By locking all the fields on the form
C. By encrypting the database
D. By hiding the delete button
Q9. Which method allows you to only display active or inactive customers in lists or combo boxes?
A. Using queries filtered on the isactive field
B. Deleting all inactive customers
C. Changing the database password
D. Compacting and repairing the database
Q10. What is a "hard delete"?
A. Physically removing the record from the database table
B. Marking a record as inactive
C. Moving the record to another table
D. Encrypting sensitive records
Q11. How can you prevent accidental permanent deletion of records by users?
A. By disabling Allow Deletions on forms and using soft delete logic
B. By giving all users admin rights
C. By only backing up weekly
D. By deleting the database regularly
Q12. How can you make a combo box list only show active customers?
A. Base the combo box on a query that only returns active records
B. Delete all inactive records from the customer table
C. Sort the combo box by inactive status
D. Use a password on inactive records
Q13. What should you consider before permanently deleting any information from a database?
A. Whether you will ever need the information in the future
B. How old the database file is
C. Whether it will be shared with others
D. How many users are active
Q14. What is an appropriate action for truly temporary or recalculable data you will never need again?
A. Perform a hard delete to remove it from your primary tables
B. Archive it to a secondary table
C. Mark it as inactive
D. Set a password for it
Q15. What does an "archive" process involve in the context of the video?
A. Moving inactive records to another table or database file for long-term storage
B. Deleting all inactive records from the database
C. Backing up the entire database every day
D. Hiding inactive records with a macro
Q16. What is a union query used for, according to the video?
A. To combine similar fields from two tables for consolidated reporting
B. To join unrelated tables by primary key
C. To split a table into two databases
D. To permanently delete duplicate records
Q17. What does setting a default value of =Forms!CustomerF!CustomerID on a combo box do?
A. It defaults the combo box value to the CustomerID of the form that is open
B. It disables the combo box for that CustomerID
C. It locks the combo box to prevent changes
D. It displays the first customer alphabetically
Q18. When is it acceptable to delete log files, bulk emails, or other temporary data from your system?
A. After you have analyzed or no longer need them for your business purposes
B. Every night automatically
C. As soon as a customer is added
D. Once you reach 100 records
Q19. If a customer is deceased or retired but must remain in the system for accounting purposes, what should you do?
A. Mark the customer as inactive (soft delete)
B. Delete the customer immediately
C. Hide the customer's name in reports with macros
D. Encrypt the customer's record
Q20. What business consideration is most important when deciding whether to delete a record?
A. Whether there is a future need for the information or a legal requirement to retain it
B. How many fields are in the table
C. The color of the forms in your database
D. Whether the record contains only numbers
Answers: 1-A; 2-A; 3-A; 4-C; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-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 video from Access Learning Zone focuses on why you should not delete records from your Microsoft Access database. This is a very common question for anyone managing a growing database, and it often comes up when you have customers or products that are no longer active, but you still need to retain their records for accounting or historical purposes.
Often, someone will ask whether it's possible to remove these records from day-to-day operations without actually deleting them from the database. My response is that you should avoid deleting records whenever possible. There are several important reasons for this.
First, let's discuss why people might consider deleting data. Sometimes it's a matter of disk space, but with how affordable storage has become, that is rarely a serious issue. Even if you are concerned with Access's two-gigabyte database file limit, you can always split your database and distribute tables across different files, or link tables externally to other database files. These are well-documented solutions.
Speed is another concern. Large tables full of inactive or unused information can slow down database performance, especially over a network and if you have indexed fields that need to be rebuilt each time you update your data. In principle, removing unnecessary data can help to keep things running efficiently.
Legal requirements can also be a factor. For example, certain privacy regulations stipulate that customers have the right to request their data be deleted. However, even then, you need to be careful. Deleting a customer record that is tied to order history can cause issues with your accounting or audit trails.
There are some situations where deleting temporary or outdated information is reasonable: old log files, email batches that are no longer relevant, or cached data that can always be recalculated. Just always remember to back up your data before you remove anything.
On the other hand, there are compelling reasons not to delete. One, you often need to keep records for accounting, reporting, or legal compliance. Deleting a customer who has order history, for example, can corrupt reporting and make legal or financial reviews impossible. You may also need to look up old data for customer service, warranty, or regulatory audits. If data is permanently deleted, this would not be possible.
It is important to distinguish between two types of deletion: hard deletes and soft deletes. A hard delete actually removes the record from the database. A soft delete simply flags the record as inactive or discontinued so it does not appear on forms, lists, or combo boxes used for day-to-day operations, but the data is still intact for historical reference.
For most situations in Access, particularly for customers, products, or employees, I recommend using soft deletes. For example, if a customer retires or passes away, or if a product is no longer sold, instead of deleting the record, mark it as inactive (or discontinued for products, terminated for employees). This prevents clutter in reports and keeps your lists relevant, while preserving all historical relationships.
To implement this, you can add a yes/no field named IsActive (or similar) to your tables. This field should appear as a checkbox on your forms, allowing you to easily mark records inactive. Set your forms' Allow Deletions property to 'No' to prevent accidental record deletion by your users. To limit which records appear on lists or in combo boxes, create queries that filter by IsActive, so only active records appear where appropriate.
With a little VBA, you can also adjust your form logic so users can filter between active and inactive records via checkboxes or other controls. This way, switching views is easy and intuitive, without exposing the database to the risk of incorrect deletions.
If you have related tables (for example, a Contacts table related to Customers), ensure that only active customers are available for selection in combo boxes when entering new contact records. This prevents confusion and keeps your data tidy.
In situations where space really does become a concern, or for even better organization, you can take archiving a step further. For those in the extended cut video, I'll show you how to add an InactiveDate field to track when a customer or entity became inactive, and how to archive information related to these records after a set period, like 30 days. We will move related records to an archive table in a linked backend database file, further reducing clutter in your main application while keeping historical data accessible if needed. I'll also demonstrate how to create union queries to report on both active and archived records together when necessary.
To sum up, the best approach is to avoid physically deleting records. Instead, mark them as inactive and control their visibility with queries and form logic. Only consider deleting or archiving when you are certain the information is no longer needed, have confirmed legal or business requirements, and have made a proper backup.
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 List Reasons to avoid deleting records in Access databases
Pros and cons of deleting database information
Business and legal considerations for data deletion
Risks of cascade deletes in relational tables
Difference between hard delete and soft delete
Implementing a soft delete using an IsActive field
Adding a Yes/No IsActive field to a table
Adding a checkbox to forms for active status
Setting Allow Deletions to False on forms
Using queries to filter active and inactive records
Creating queries for active customers
Creating queries for inactive customers
Filtering form lists and combo boxes by active status
Adding a ShowActive checkbox on a navigation form
Passing checkbox value to form as a WHERE condition
Updating forms to display filtered records
Handling related tables, such as Contacts linked to Customers
Building a Contact table with foreign key relationships
Creating a simple Contact form
Replacing CustomerID field with a combo box
Building a relational combo box for Customer selection
Linking Contact form to display records for a specific Customer
Using WHERE condition with DoCmd.OpenForm for filtering
Defaulting combo box value based on open forms
Adding Add New Contact functionality on the main menu
Populating combo boxes with only active customers
Naming controls for clarity in code
Limiting combo box lists to active customers only
Opening forms in Add mode for new records
Summary of best practices for soft-deleting and filtering records |
