Reset Database
By Richard Rost
11 months ago
Delete Data & Reset AutoNumbers for Blank Database
In this Microsoft Access tutorial, I will show you how to reset your database by deleting existing data and resetting auto number fields to start from one. This is especially useful if you need to create a blank copy of your database for distribution or demonstration purposes. We'll begin with a manual method suitable for beginners, then explore a more advanced technique using VBA to automate the process. You'll learn how to ensure your data is backed up, delete records from tables, and use Access's compact and repair feature to reset auto numbers, providing a fresh database experience for new users.
Richard from Concord, North Carolina (a Silver Member) asks: I made this database for personal use, but now others want to use it too. How can I delete all the records to make the tables fresh again and reset the AutoNumber fields to start over from 1?
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, reset auto number, delete database records, compact and repair, Access developer trick, Access macro for delete, SQL delete query, VBA compact database, Access backup database, compact and repair in VBA, delete queries with macros, Access compact and repair, database reset button, auto number reset tutorial, Access developer method
Subscribe to Reset Database
Get notifications when this page is updated
Intro In this video, we'll look at how to reset your Microsoft Access database to create a blank copy for distribution or demonstration purposes. I'll show you step by step how to delete all the records from your tables and use the compact and repair feature to reset auto numbers back to one. You'll learn both the beginner manual approach and, for those comfortable with a little code, an efficient way to do this with VBA and a reset button, including safeguards to prevent accidental data loss. This will help you provide a clean version of your database to others whenever you need.Transcript Welcome to another TechHelp video brought to you by Access LearningZone.com. I am your instructor, Richard Rost. Today we're going to see how to delete the data in your database and reset the auto numbers back to one if you want to create a blank copy of your database to give away to other people, to distribute it, or to showcase in the World Microsoft Access Museum that I'm sure they're building right now. And do other cool stuff with it like that.
Today's question comes from Richard in Concord, North Carolina. Cool name. One of my silver members. Richard says, "I made this database for personal use, but now others want to use it too because it's awesome, I'll bet. How can I delete all the records to make the tables fresh again and reset the auto number field to start over from one?"
Well, first I want to remind everybody that that auto number you shouldn't care what it is, whether it's one or 65 or 85,612. The right that number is meaningless to you or the user. The only person that cares about that is your access database, and yes, the Access database is a person too. If corporations can be people, access databases can be people. Anyway, I got a whole separate video on why you shouldn't care what those auto numbers are. So go watch this if you want to learn more.
Now with that being said, yes, I know when you give away a fresh copy of the database, you want those auto numbers reset so people don't care. Well, that's the easy thing. All you basically have to do is delete the data from the tables that you don't want in there, like your customers, your orders, whatever else, and then compact and repair the database. And when you do that, Access will reset all of the auto numbers.
So if you got 100 records in your customer table and you delete them all, well, let's say you delete 90 of them. You want to keep 10. If you compact and repair, Access will reset that auto number back to 11, one higher than the highest value in the table. That's just how Access works.
So let me show you the beginner method to do it first, the easy way, the manual way. And then at the end of the class, I'll show you a nice developer trick. We can make one button that will go through and do all that for you. It'll delete all the values and then it'll remind you to compact the database. Yeah, you can compact with VBA, but an open database can't compact itself. That's a whole separate. You got to use a second database. I'm going to do a video on this in the future. It's a bit of a pain, but at least I can pop up a reminder for you.
So let's look at the beginner method first. First, a little prerequisite. If you haven't watched my compact and repair video, go watch this. It explains all what happens when you compact and repair a database. And I'm assuming you know all the basics, how to use tables and delete records and stuff like that. If not, go watch my free Access Beginner 1 class. There's the link.
Before we get started doing anything, of course, back up your database before you do anything to it. Make a copy, put it in a folder somewhere else, put it on an external hard drive, put it in your Google Drive folder, whatever you got to do. Back up your database, watch this video for more information on how.
All right. So here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want, so you'll find a link down below. And in here, I've got four tables. I got customers, contacts for those customers, ordered details and orders. All right. Those are my four tables.
Now let's say you want to delete all the data from these tables. Well, it's really easy. You just come in here and you just click on this little box right there that will select all the records and you hit delete. That's it. You just do that for each one of these tables. All right. But now look, if you come in here and you type in somebody new, they're at record 34 already. And again, you shouldn't care what number that is, but a new user is going to be like, why is it 34 and not one?
Okay, fine. So what we do in that case is let's delete this one. Okay. And then we'll come up to database tools, compact and repair. And now if you go into customers and you come in here and you put somebody new in, it's one. And if they get an order, oh, I didn't delete these records. Okay. So we'll delete these records here too. Let's compact and repair again. All right. Now if I come into orders and put an order in for customer one, that's back to order ID one. So now you got a fresh new blank database.
Okay. That's it. That's all you have to do. Now, if you got a lot of tables, that can be a bit of a pain. You can make delete queries, a bunch of delete queries for each table that will go through and delete the records of those tables and you can string them all together with a macro. If you don't want to get into VBA programming, I think that's actually a lot more work. But if you want to learn how to make a delete query, which basically deletes records from your database, then there you go. There's a video for you.
All right. So that was the easy way that I'm going to do it now. If you do this once in a while, no big deal. Just go with you. If you got a bunch of tables in here and you don't feel like making a macro and a bunch of delete queries, you can do it with a little bit of VBA. It's real simple too.
Now for those of you who don't know VBA, go watch this video. It'll teach you everything you need to know to get started in about 20 minutes. But let me grab a fresh copy of my database. I always keep a copy on my desktop because I use it in pretty much every video. Let's open it back up again, make sure we got some data in here. Okay, we got everybody in here. Sometimes what I like to do when I used to distribute databases is I would always leave myself in there as record one. So when we write our delete query in our code here, we'll leave number one in there. All right. So how do we do it?
Well, we're just going to go into a button. We'll take this button here. All right. We'll call it reset database. Obviously, put this button somewhere hidden, like a hidden form for just you so that your users don't get into it or give them the option to reset the database if they want to as well. I don't care. That's up to you. But how do we do this? All right. So down here, we're going to say status, deleting data.
All right. What is status? Status box function. Everybody always asks about it. So I got a whole separate video on it. It basically just uses this box here to put notes for the user in there. It prevents you from having to message box all the time. Now in here, we're just going to write some basic SQL statements using currentdb.execute. You can also use do command.run sql, but this will avoid a lot of the pop-up warnings and stuff. So current, the current, I can't spell it in currentdb.execute. And then we'll put our sql in here, delete from customerT. And I'm going to say where customerID is greater than one that'll keep customer one. That's me.
Okay. Then you basically do the same thing for all the other tables. You say currentdb.execute. Delete from contactT. That's it. And you just copy and paste this. Copy. Paste. Paste. T. We're going to do order detailT. Whatever other tables you want in here, to delete. If you got like helper tables with like list of states and whatever, save those. All right. Give those to your user. That's fine. But delete anything that might have personally identifiable information. And then when you're all done, you can status done. And you can say maybe give it a beep. And that's it. That's all you need right there.
Now, this is dangerous, however. So what I like to do is I like to throw some warnings up top here. All right. Something like if message box, this will delete all data from the database. Database. Are you sure? All right. And then we'll do VB. Yes. No. Cancel. Plus. VB default button two. So if they just press enter, it's going to give them a no instead of a yes. Right. And then I'll do a VB critical on that, which pops up a little warning and gives you a little exclamation point. Let's line continue this guy. And then come on, the title will be delete like that. Right. Now, if they answer anything but yes, we're just going to exit the sub. So if that's not VBS, then exit sub.
So we're done. Let's go to my second challenge. Because this is important stuff. If they're going to delete all the data in the database, you want to make sure that they're sure. You can give them another message box. Are you really sure? I like to make them type in the word delete. So for this, we use an input box. So we'll say if input box, right, type in the word delete to continue. Right. And then comma, what's the title delete same, same title. Okay. And if that is anything other than delete, it's not case sensitive. That doesn't really matter that much. Right. Then we're going to message box incorrect. And then exit sub. Now if they did type it, if they said yes, and they typed in delete, then your code's going to run.
See, a lot of this is user packaging. Right. And especially if it's, I mean, if it's for somebody else, that's important. If it's for you, you might not need all that. It's fun to do. Anyway. Save it. Debug compile once in a while. Make sure your code's good. It looks like it is. Close that. Close that. Before you run it, again, make a backup copy of your database. It's this easy, folks. Even if it's open, you should be able to just go like this, right-click and drag copy here. Boom. I got a copy of my copy. It's that easy. Whenever you're working on your database, whenever you're making major changes, copy your database. Right. And I also have Google Drive backing up my stuff too. All right.
But now let's verify. I got here my customer table. Here's my order table. Okay. Close it. Close it. Let's go to my main menu. Hit the button. Oh, this will delete all the data. Are you sure? Oh, wait. No, I'm not sure. No. Okay. Didn't do anything. Right. Be good. All right. Let's say yes. Oh, type in the word delete to continue. Uh. Okay. It, okay. Incorrect. All right. Yes. Type in the word delete. And then press enter. Boom. Done. Check your tables. I'm the only one left. All right. You could also put a reminder in here to compact your database. All right. Orders are clear. Contacts are clear. Yeah. I'd do that too. I'd stay in here. Done. Remember to compact and repair your database. Right.
And now when it runs, yes. Delete. Remember to compact your repair database. Or you can message box that if you want to. And that was just one more click, click. And we're compacting a repair. Right. That's it. And yeah, I'm going to make a video in the future to show you guys how to do the compact and repair in code. That has pretty much it. That's how you do it. That's the beginner and the developer methods for you. So make everybody happy.
If you want to learn more, got tons of lessons on my website, tons of beginner lessons, expert lessons, which is more than beginner, but not quite advanced to developer yet, advanced lessons cover macros and events, which if you don't want to become a programmer, you want to just learn to macro basics, advance this for you. And of course, developer, I got like 50 levels of developer courses. I got, you name it, I cover it. It's in here. It's like, what's that spaghetti sauce? The prego, it's in there. Right. I got it. Think I'm up to what, 50 now? It's getting there. But that's going to do it for today, folks.
That is your TechHelp video. Hope you learned something. Live long and prosper my friends. I will see you next time. If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free. And make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access Experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that show more link right there. YouTube's pretty good about hiding that, but it's there, just look for it.
Now, if you have not yet tried my free Access Level 1 course, check it out. Now, it covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you and click on and did I mention it's completely free? And if you like level one, level two is just $1. That's it. And it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut tech help videos, and there's hundreds of them by now. They also get one free beginner class each month. And yes, those are from my full courses. Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. And gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses, all of them from every subject. And you get one free advanced or developer class every month after finishing the expert series. And you can become a diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper my friends. I'll see you next time.
TOPICS:
Resetting auto numbers in Access
Deleting all records in tables
Compact and Repair Database
Beginner method to reset auto number
Create a blank copy of a database
Delete data using delete queries
Using VBA to delete records
Generating a button to delete records
Conditions to prevent accidental data deletion
Using SQL in VBA to delete records
Adding user warnings in UI
Input box for delete confirmation
COMMERCIAL:
In today's video, we're going to learn about resetting your Microsoft Access database to create a fresh copy for distribution. I will show you how to delete data and reset auto numbers by using the compact and repair tool. We'll cover both a beginner's manual method and an advanced developer trick using VBA to streamline the process with a single button. This way, you can maintain a clean database without losing your data integrity. Remember to always backup your database before making major changes. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary topic of this video tutorial?
A. Creating tables in Microsoft Access
B. Resetting AutoNumbers and deleting data in Microsoft Access
C. Integrating Microsoft Access with Excel
D. Designing forms in Microsoft Access
Q2. What is the function of the "compact and repair" tool in Access, as discussed in the video?
A. It is used to back up the database
B. It resets AutoNumbers and optimizes the database
C. It converts the database to a different format
D. It synchronizes data between two databases
Q3. Why should users generally not care about the specific values of AutoNumbers in Access?
A. They are always sequential and predictable
B. Access assigns them randomly, so they are unpredictable
C. The AutoNumber is only a unique identifier and does not have any other significant meaning
D. They may change each time data is added
Q4. What should you do before deleting data from your Access database as recommended in the video?
A. Ensure all users are logged out
B. Compact and repair the database first
C. Make a backup copy of the database
D. Turn off database caching
Q5. What type of query can be used to remove records from tables in Access?
A. Update query
B. Select query
C. Delete query
D. Append query
Q6. Which option can be used to automate the process of deleting records using a button in Access?
A. An Excel macro
B. A VBA script attached to the button
C. A built-in Access wizard
D. A database merge function
Q7. When setting up a process to delete data using VBA, what precautionary measure is suggested?
A. Running a virus scan beforehand
B. Asking for confirmation with a message box and having users type 'delete'
C. Scheduling the task during off-peak hours
D. Only allowing administrators to run the process
Q8. What is one way to help identify if a process in Access has completed successfully, as mentioned in the video?
A. Sending an email notification
B. Displaying a status message on the screen
C. Creating a summary report
D. Logging out all active users
Q9. What does the video suggest doing with tables that contain personal data when creating a distributable database?
A. Ensure they are encrypted
B. Delete any personally identifiable information
C. Archive them to a separate database
D. Create a read-only copy
Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-B; 7-B; 8-B; 9-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is all about preparing your Access database for distribution. I'm your instructor, Richard Rost, and I'll be guiding you on how to delete data in your database and reset the auto numbers to start from one. This can be useful if you want to share a blank copy of your database with others or if, perhaps, you're aiming to showcase it in a hypothetical Microsoft Access Museum.
A question from one of our members, Richard in Concord, North Carolina, prompted this session. He made a database for personal use, but others are interested in using it. He wants to know how to clear the records and reset the auto number fields.
Firstly, I'd like to emphasize that the auto number field in Access should not hold significance for you or the user, as its primary purpose is for the database's internal use. However, if you're preparing a fresh copy of the database as a giveaway, it's understandable to want those numbers reset.
To achieve this, you can manually remove the data from the tables that you wish to clear, such as customers and orders, and then use the compact and repair function. This process resets the auto numbers. For example, if you delete all but 10 records from a table, compacting and repairing will reset the auto number to start from 11.
Here's how to execute this manually: Select all records in the table and delete them. Then, run the compact and repair tool. Once done, when new data is entered, it will start from one again. This is a simple method but can be tedious if you have many tables.
For a more streamlined approach, especially if you need to do this regularly, you can automate the process using VBA code. With a single button, you can delete all values, remind yourself to compact the database, and even prevent the database from compacting itself if it's open. I'll create a future video on how to handle compacting with code.
Before beginning, ensure you have backed up your database. This is crucial to prevent data loss. Make a copy and store it safely, whether on an external drive or a cloud service.
In my TechHelp free template, I have four tables: customers, contacts, order details, and orders. To remove all data, simply select all records from each table and delete them. Auto numbers will jump to the next available number unless you compact and repair first.
For frequent use, I recommend setting up a delete query for each table to automate data removal, which you can execute using a macro. Although more advanced, this method doesn't involve extensive VBA programming. If you're keen on scheduling or learning VBA, refer to my essential video guide about it.
In the manual approach, code can be employed to develop a reset button. This button, possibly hidden for end-user safety, will facilitate easy deletion of selected records. Ensure that the deletion process includes warnings and confirmations, such as requiring the user to type in "delete" to proceed.
Afterward, remind yourself to repair. One way is to have a reminder popup post-deletion to instruct you to compact the database.
In conclusion, resetting your database involves a committed process of deleting records and utilizing compact and repair. For further tutorials, I have lessons ranging from beginner to advanced developer levels. My website hosts an array of courses, ensuring you find one suited to your needs.
For a complete video walkthrough of today's tutorial, including step-by-step instructions, please visit my website at the link below. Live long and prosper, my friends.Topic List Resetting auto numbers in Access
Deleting all records in tables
Compact and Repair Database
Beginner method to reset auto number
Create a blank copy of a database
Delete data using delete queries
Using VBA to delete records
Generating a button to delete records
Conditions to prevent accidental data deletion
Using SQL in VBA to delete records
Adding user warnings in UI
Input box for delete confirmation |
