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.