Delete Sample Data
By Richard Rost
3 years ago
Delete the Sample Data From Your Database
In this Microsoft Access tutorial I'm going to teach you how to delete the sample records in your database before you release it for distribution. We'll learn about Delete Queries and how to automate those using some VBA. Then we'll change the Delete Queries over to SQL statements. We'll also ask the user if they're sure by making them type in the word "delete."
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, delete data from database, delete query, delete sql
Intro
In this video, I will show you how to quickly delete sample data from your Microsoft Access database before releasing it, so you can keep your development records without sharing them with others. We will look at deleting records from specific tables using delete queries, discuss which tables and data should be kept, and then automate the process with a VBA button that includes a user warning. I will also show you how to use SQL statements in VBA to make deleting data even more efficient, and explain why you might want to compact your database afterward.
Transcript
Welcome to another Fast Tip video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to delete the sample data out of your database before you release it.
Let's say you spend some time and build yourself a cool little database. You want to release it to your friends, your family, your co-workers, or whoever, but you have a bunch of records in it. Like me, I have a bunch of sample records in mine. I don't want to necessarily have everybody get a hold of all my sample data, but I want to keep a copy of it for myself so that I can still use it for future development.
I have my contacts all in here, my customers, my orders, and details - stuff that I don't necessarily want going out in the final product. I also have some tables, like the version history table, that I want to leave. You might have tables with supporting data in them that's valuable, like a table with a list of states, regions, zip codes, or whatever.
So, some of this data has got to go, and some of it has to stay. You don't want to have to go in here and open each table, select all the records, and delete them every time you have a release. I used to have some databases where I'd post releases a couple of times a week, so I wanted one button to click that just does the job for me.
I am going to show you a simple way to do it, and then I am going to show you a better way to do it. Now, I didn't stamp the good old "developer" thingy here because you can do this without coding, but it's a little better if you do it with some coding. So, I will show you a way to do it without coding first, then I will show you how with just a couple lines of VBA, you can do some really cool stuff.
A prerequisite for this video: if you haven't watched my Delete Queries video, go watch that first. This is what we're going to use. A delete query is a query. Not all queries are for viewing and sorting data; some are called action queries and they can do stuff. They can change your data, they can add records, update records, make whole tables, and delete records. That's what a delete query does. This is important, so go watch this if you don't know what a delete query is.
If you want to learn a little VBA and you've never done VBA before, go watch this. It's 20 minutes long and teaches everything you need to know. It's just a couple of lines of code, but you have to know where to put them. I'll show you in just a minute.
The first thing is, just pick any tables where you need to delete all the information, like the order table, for example. All this stuff has to go. Order detail has to go. So we're going to create delete queries. We're just going to do one for now. Actually, I am going to do two delete queries just to show you the simple way to do it.
Go to Create, Query Design (we've done this before in the delete query video). Bring in the order detail table. It's always best to delete the child records first, especially if you've created relationships and enforced referential integrity. Then, you can't delete orders if they have details, so I always like to start with the bottom-most child records first and then go backward. I don't usually enforce referential integrity in my databases. Why? It's a long story; I'll talk about it in a video. But we'll start with this one, and we just want to delete everyone out of here.
Change this to a delete query and then bring in the star. Run it, and that's it. Order details should be empty. Yes, it is.
Save this as OrderDetailDeleteQ. There you go - there's that one.
Then, you'll do the same thing for the order table and the contact table. Customer table I like to do a little bit different because I always leave my record as record one in every database that I send out for a couple of reasons. One, it shows some sample data, and two, it gives you my contact information in case you need to get a hold of me.
There's my address, my actual business phone number, and my actual email address is in here somewhere. I leave it in there. So I'm going to make this delete query and leave record one.
For this, go to Create, Query Design, bring in the customer table, change it to a delete query this time, bring in the customer ID. Where criteria is not one, we say <>1. This is not one.
Now if I run this and take a peek at customer table - oh look, there's just me left. Delete all the records that are not customer ID 1. Save it as CustomerDeleteQ.
Now we have two of them in there. You will do the same thing for every different table that you want to delete from. Leave some tables alone, like my version history table, your state table, your zip code list table, or whatever supporting data that you do want to give to your end user.
If you don't want to do any VBA coding, that's it. You'll just have to go and manually run these delete queries by yourself. Sit here and just double-click on each one, and it will delete those tables, and then you're done.
But if you want to learn a little bit of VBA, you can do some fun stuff. Let's make a button that will do it for us. I will just utilize this button. You're probably going to want to put this button somewhere like a manager menu or tuck it away hidden somewhere.
I'm going to say Delete Sample Data. This is not only good for deleting records to distribute the database, but also if you have a super secure database with lots of sensitive information, and you want to give a copy of it to someone else for database structure purposes, then you don't have to give them all your data. I used to tell customers when I did consulting work to delete any sensitive information out of it. Just leave a couple of sample records so I know what's going on, but I don't want all of your sensitive data.
In this button, right-click and build event.
Here we are. We're in the Hello World button click - that's fine. Let's just give it code.
Now to run those update queries that you made, it's this simple: it's DoCmd.OpenQuery and then the name of the query - CustomerDeleteQ, right? No single quote.
Then the next one would be OrderDetailDeleteQ, and that's it. When you're done, show a status of "done."
That's the minimum of all you'd have to do right there. If I close this and just test it, I'll put another customer in here real quick.
Let's go test. Main menu and test - ready, click Done. Open up customer and there it is. That's the simplest way you could do it if you just want to OpenQuery all of those.
But wait, there's more. Let's have some fun. Let's do some more.
Next, before someone accidentally clicks the big red button that says "Do Not Push This Button," I want to give them a warning message. In a previous video, I showed you how to do an "Are you sure?" message box. I will put a link to it down below. You can pop this up and it will say "Are you sure?" but I don't want to make it that easy for the user to delete all of the data in the database.
We're going to make them actually type in a word, for example, "delete." For that, we're going to use an input box where we actually have them type in something. I used it in this video to have them type in a manager password.
Up top here, we're going to Dim s As String, making our own variable called s. Then, s = InputBox. What's the prompt? "Warning: this will delete all data in the database. To continue, type 'delete'." Put "delete" inside single quotes. They will have to type the word "delete" (do not put the single quotes there too). The title is "Delete", no default value or anything else.
It's going to prompt the user: "This will delete all data in the database. To continue, type 'delete'." They type something in and press Enter, and that's going to return a value and put that into s. Then we say, if s is not equal to "delete", then Exit Sub. That means they are not sure, or they did not type "delete".
If you've never worked with variables or If Then statements before, I have videos on that, and I'll put links down below.
Assuming they do type "delete" and get past that point, now you can go ahead and run your delete queries.
Save that, come back out here, click the button. "Warning: this will delete all data in the database. To continue, type delete." If I type in "cat," nothing happens. Do it again, type in "delete" - it's not case sensitive. Enter, and then it's done.
You can make it case sensitive by using a string comparison function - that's a different video.
That shows you how to make sure the user is sure, run your deletes, fill your deletes here, and then done.
Now, if you want to be even cooler - there's a way to do this without having to make all of these different delete queries. That's to learn a little bit of SQL.
SQL is the language that powers these queries, and Access does a fantastic job of giving you this nice, pretty query design view. It's beautiful, it's graphical, it's how I learned. I learned how to do this years before I finally learned SQL.
But behind this, if you right-click and go to SQL View, is this: it is a language, and it's real simple to use, not that hard. "DELETE FROM OrderDetailT" - that's it, that deletes the records. The other one's a little more complicated, my customer one, because it has a criteria on it.
SQL View: "DELETE FROM CustomerT WHERE CustomerID <> 1". It's not hard. You want to learn a little bit, I have a video for you. Watch this video. It's about 20-25 minutes long. It covers the basics of SQL. I wish I would have had this when I was getting started because SQL did not come easy to me. I learned basic programming when I was like eight years old, so programming line by line was a lot easier for me. The concept of database queries and SQL was harder for me when I first learned it. But now it's easy once you get the hang of it, and I'll help you get the hang of it.
Now we can get rid of these delete queries. We deleted the delete queries - see what we did there?
There are two ways you can run SQL statements in your code. You can use DoCmd.RunSQL, which has its benefits, or another way, which is CurrentDb.Execute. I prefer CurrentDb.Execute for a lot of reasons. I have a whole different video coming out on that for more advanced students, comparing which one is better and why. For now, just trust me, I like this one better.
You put in your SQL statement to delete whatever you want to delete, like:
DELETE * FROM OrderDetailT
Just do a line like that for each of your SQL statements. For example:
DELETE * FROM OrderT
DELETE * FROM ContactT
And finally, for the customer table:
DELETE * FROM CustomerT WHERE CustomerID <> 1
In some versions of SQL, like SQL Server, you don't need that asterisk. I like to leave it there. SQL is slightly different depending on what platform you are on - there are ANSI 92, 89, and other standards. In these videos, I focus on SQL for Access. If we're doing SQL Server stuff, I will let you know.
Now, right-click, delete, and we're done. All these tables should now be empty and ready for distribution.
One more thing you might want to do before you're done is compact the database. You had all that sample data in there, so let's get rid of all that empty space. If you don't know what compacting your database is, go watch this video. To do that, just come up to Database Tools, click Compact and Repair, save changes, and it'll close and reopen.
Yes, you can compact and repair from VBA, but there are some tricks because you can't compact and repair the currently open database from VBA without closing it and then opening up another database or saving it as a different file. I have a video on this coming out, as well as a template to show you how to compact and repair all of the databases in a folder. Look for that in the next couple of days.
There you go. There's your Fast Tip for today. I hope you learned something, and I'll see you next time.
Quiz
Q1. What is the main goal of the video tutorial?
A. To show how to import sample data into a database
B. To demonstrate how to delete sample data before releasing a database
C. To explain referential integrity in relational databases
D. To teach advanced VBA programming
Q2. Why might you want to keep some data, like version history or state lists, when preparing a database for distribution?
A. Because all tables must have data
B. Because supporting data may be valuable for end users
C. Because database structure depends on filled tables
D. Because queries require data to function
Q3. Which type of query is used to remove records from an Access table?
A. Append query
B. Update query
C. Delete query
D. Make-table query
Q4. When deleting records from related tables, which should you delete first?
A. Parent records
B. Child records
C. All at once, order does not matter
D. Tables with the least data
Q5. What is the advantage of using VBA to run multiple delete queries?
A. It adds extra security to the database
B. It allows you to delete records without user confirmation
C. It automates the process, requiring only one button click
D. It prevents any accidental deletion
Q6. Why might you want to leave a specific record (such as record one) in the customer table when releasing a database?
A. To comply with database requirements
B. To provide sample data and contact information
C. To enable referential integrity
D. To avoid table corruption
Q7. What method is used in VBA to run a saved query?
A. DoCmd.OpenForm
B. DoCmd.OpenQuery
C. DoCmd.RunSQL
D. CurrentDb.Execute
Q8. How can you add extra protection before running mass-deletion actions from a button?
A. Remove the button from the interface
B. Require the user to type in a specific word using an input box
C. Hide the button in the navigation pane
D. Password-protect the form
Q9. What is needed for the input box confirmation to allow deletion to continue?
A. The user must click OK quickly
B. The user must type 'delete'
C. The user must leave the field empty
D. The user must type any text
Q10. What language is used behind query design in Access and is responsible for instructions like 'DELETE FROM OrderDetailT'?
A. HTML
B. VBA
C. SQL
D. XML
Q11. What is Richard's preferred way to execute SQL commands in VBA for deleting records?
A. DoCmd.OpenQuery
B. DoCmd.RunSQL
C. CurrentDb.Execute
D. Direct table editing
Q12. What action should be performed after deleting a lot of data from your database, according to the tutorial?
A. Backup the database
B. Reindex all tables
C. Compact and repair the database
D. Encrypt the database
Q13. What is the limitation of compacting and repairing a database from VBA as mentioned in the video?
A. It can only be done on external databases, not the currently open one
B. It only works on tables with less than 1000 records
C. It is not possible without internet connection
D. It cannot be done with sample data present
Q14. Why is learning SQL useful according to the video?
A. It is used for database security
B. It provides a universal language to manage and manipulate data in queries
C. It replaces the need for tables
D. It is required for all VBA commands
Q15. What should you do if you are unfamiliar with Delete Queries or basic VBA before following the tutorial steps?
A. Skip those steps
B. Look up information online
C. Watch the recommended introductory videos first
D. Ignore and attempt the actions anyway
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-C; 11-C; 12-C; 13-A; 14-B; 15-C
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 an important step you should take before distributing your Microsoft Access database: removing any sample data you may have used during development. Often, after you spend time building out a database, you have test records in various tables like contacts, orders, and other sample information. While this data is useful for development and testing, you probably do not want to share it with everyone who receives the database. At the same time, you might want to keep a personal copy for further development work.
There are typically tables in your database that will need to be cleared of data, such as customers or orders, and others you might want to keep intact, like a list of states, regions, version history, or other supporting data used throughout the database.
Manually deleting records from each table would be inefficient, especially if you have to do this before every new release. A better approach is to automate the process. In this lesson, I cover two ways to handle this task: a simple, non-coding method using delete queries, and a more advanced, streamlined approach using some basic VBA.
A foundational skill for this is understanding action queries in Access, specifically delete queries. If you're not already familiar with them, I recommend reviewing my lesson on delete queries first. Unlike select queries, which just view or sort data, action queries can add, update, or delete records in your tables.
Start by identifying which tables you want to purge. For example, both order and order detail tables usually need to be cleared. The order of deletion matters, especially if you use relationships and referential integrity. Always delete child records first, then their parent records, to avoid errors. For instance, remove items from the order detail table before deleting anything in the orders table.
You can build a delete query by going into Query Design, selecting your table, switching the query type to Delete, and adding the entire table's fields to the grid so all records will be deleted. Save this query with an appropriate name such as OrderDetailDeleteQ. Repeat this process for each table you want to clear, like the order and contact tables.
Sometimes you want to keep a specific record. For example, I always leave record one in my customer table, which includes my own contact information. To do that, set a criteria in your delete query so it only deletes records where the CustomerID is not equal to one. Save this new query as CustomerDeleteQ.
You now have a set of delete queries for each relevant table that you want cleared. Tables containing reference or supporting data, such as a list of states or version numbers, should be left untouched. Without using VBA or macros, you would run each query manually to clear the tables.
The next step is to automate these deletions with a simple VBA routine. You might want to place a button on a secure or hidden form, allowing you to initiate the data deletion with a single click. Label the button with something clear, such as "Delete Sample Data." This is useful not only for final database distribution, but also when you need to give someone a copy of your database structure without your sensitive or proprietary data.
To execute this from a button, open up its event handler in the code editor. Use a simple command to run each delete query you previously created. Once the queries have finished, display a "done" message to let the user know the process is complete.
For added security, especially to prevent accidental data loss, you can prompt the user with a confirmation box. Requiring them to type in a specific word, such as "delete", before proceeding adds an extra layer of protection. Access provides an input box for this kind of prompt. If the correct word is not entered, the routine simply exits without deleting anything.
If you'd like to streamline things even further, you can write the SQL delete statements directly in your VBA code and execute those commands. This removes the need for separate delete queries altogether. SQL is quite simple for these cases. For example, to delete everything from the OrderDetail table, use a statement like "DELETE FROM OrderDetailT". To delete all customers except the first, you'd use "DELETE FROM CustomerT WHERE CustomerID <> 1". Write these as strings in your VBA procedure, and use either the RunSQL or the CurrentDb.Execute method to execute them. I generally prefer CurrentDb.Execute for reasons related to efficiency and error handling.
Before sending out your cleaned-up database, it's a good idea to compact it. This will reclaim space that was used by your deleted test records, making your database smaller and more efficient. You can do this from the Database Tools menu by selecting Compact and Repair. You can also automate compacting via VBA, but there are some tricks with doing that on an open database that I demonstrate in another lesson.
That wraps up today's Fast Tip. 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
Identifying tables with sample data to delete
Creating delete queries for specific tables
Order of deleting child and parent table records
Saving and running delete queries in Access
Excluding specific records using criteria in delete queries
Leaving supporting and system tables untouched
Manually running delete queries for data cleanup
Automating delete queries with a VBA button
Writing VBA to run multiple delete queries
Prompting users with an InputBox before deletion
Using If Then statements in VBA for confirmation
Deleting records with SQL statements in VBA
Running SQL DELETE statements using CurrentDb.Execute
Differences between DoCmd.RunSQL and CurrentDb.Execute
Compact and Repair Database to remove empty space
|
