Delete Files
By Richard Rost
2 years ago
Delete Temporary Files Automatically with VBA Kill
In this Microsoft Access tutorial, I'm going to teach you how to delete files using the VBA Kill command. Then I'll show you how to automatically delete temporary files when you close your database.
Adrian from Berwyn, Illinois (a Platinum Member) asks: I'm using the code that you showed in your "Export Report to PDF" video, and it works great, thank you! I'm able to send these PDFs in emails, but, after doing a bunch of them, I've got all these PDF files sitting around in my database folder. Is there a quick and easy way to delete them all from my database?
Members
Members will learn how to use a PowerShell script to delete files and send them to the recycle bin, which the normal Kill command does not do.
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
Recommended Courses
Code Vault Link
Keywords
TechHelp Access, VBA Kill command, delete temporary files, automatic file deletion, VBA file handling, on close event, delete PDF files, automate cleanup, Export Report to PDF, manage database folder, file deletion in Access, Access database cleanup, temp file cleanup, Order Invoice report, CurrentProject.Path, wildcard deletion, recycle bin
Subscribe to Delete Files
Get notifications when this page is updated
Intro
In this video, I will show you how to use VBA in Microsoft Access to automatically delete temporary PDF files with the Kill command. We will walk through creating and exporting PDFs, then add code to clean up these files either with a button or when the database closes. I will also explain important precautions when using the Kill command, such as handling errors and understanding that deleted files do not go to the Recycle Bin.
Transcript
Today we're going to learn how to delete files using the VBA Kill command. If you're like me, when you're using your database, you've got all kinds of temporary files left over at the end of the day, like PDF files, little text files, log files, all that kind of stuff. I'm going to show you how to clean it up when you close the database with a little bit of VBA.
Today's question comes from Adrian in Berwin, Illinois, one of my Platinum members. Adrian says, I'm using the code that you showed in your Export Report to PDF video and it works great. Thank you. You're welcome. I'm able to send these PDFs in emails, but after doing a bunch of them, I've got all these PDF files sitting around in my database folder. Is there a quick and easy way to delete them all from my database?
Yes, of course there is. There's a command called Kill that you can use. I hope my video doesn't get flagged because I use the word Kill. It's a VBA command, people. It's called Kill. It will delete whatever file you tell it to delete, which can be good usually, but it can also be dangerous, so be careful. Don't delete your database. It won't delete open files. That's a good thing. If you're working with your database, it won't delete that.
Let me show you how to do it real quick. First, if you haven't watched my Export PDF video, go watch this first. I'm going to walk you through it real quick. I'm just going to give you the basics, but this video goes into a lot more detail in case you've never done this before. And in the members extended cut, I show you how to export a batch of PDF files. That's pretty cool.
Of course, we will be using VBA in this class - not much, like two lines of code maybe. Well, we're going to export the PDF as two lines of code and then to delete that is going to be only two lines of code. But you have to know where to put this code, so go watch my Intro to VBA class. If you haven't watched this yet, it's free. It's on my website. It's on my YouTube channel. It shows you everything you need to know to get started with VBA in about 20 minutes. So go watch that and then come on back. We'll wait for you.
Here I am inside my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. In here, we've got customers and customers can have orders, and each order can have an invoice. You click that button there and it generates a nice little print preview.
Now, what if you want to do stuff with this? You want to zip it up and email a bunch of them? So you might want to export this as a PDF. It's a lot to open up the print preview and then hit the PDF button and so on. So you can make a button to do this for you. That's what we covered in the other videos.
So, Design View. I am going to slide this over here and just move Notes down out of the way, then let's copy this and paste it. We'll call this the Export as PDF, Export PDF button. Give it a good name so Alex doesn't yell at us: ExportPDF_Button. Right click, Build Event. This is pretty much what I covered in the other video.
First thing we need, just like when we generated the output report here, we want to Me.Refresh first. Me.Refresh makes sure that if you made any changes on the form they get saved, because when the report opens, it's going to get the data out of the table instead of what happens just being the form. You have to make sure your form isn't dirty.
Then, to export this, we're going to go DoCmd.OutputTo acOutputReport, and then the name of the report. What are we outputting? Well, it's going to be OrderInvoiceR, which is the same thing that normally opens up in print preview mode. We're just going to send it to a PDF.
What's the output format? They don't give you a pick list here, a drop-down list with IntelliSense. You just have to know what it is. I cover this in the other video. It's acFormatPDF.
What's the output file name? Let's go to a new line here using underscore. I want to put this in the current database folder, so that's CurrentProject.Path. That's whatever your path name is to wherever your database sits, so it's C:\\wherever.
Tag onto that a backslash, and then I'm going to make this be like Order1.PDF, Order6.PDF, Order whatever the OrderID is .PDF. So it's going to be Order- and OrderID, which it will get off the currently open form, and .PDF.
So, you build that file name together. If the CurrentProject.Path, that's the folder the database is in, and backslash Order-3.PDF or whatever it is. If you want to be able to see it, if you want to preview it when it opens, add comma True here; if not, set it to comma False. I want to take a look at it when it comes out.
So there's your command right there. That's what I covered in the other video, plus a bunch of other stuff, but this will get the job done.
Quick Debug Compile, let's go back to the database, close it, save changes, yes, open it up and hit the Export to PDF button. Click. It happened and it opened up in my web browser, which is my PDF viewer. You might have Adobe Acrobat or whatever. I use my web browser to view PDFs. There it is. There's my PDF.
Now, it's been exported. It's sitting in my database folder, which for me is my desktop. When I'm making videos for you guys, I just have my database sitting here on my desktop. There's the lock file and there's my Order1.PDF.
We're doing some more stuff here. I go to somebody else. There's another one here. Export that one. Go to somebody else, open up their orders. They don't have any orders. Let's find somebody else with an order. We'll export that one.
We're sending out some stuff, we're doing some things, and now at the end of the day, my database folder is getting all cluttered with all these files. It would be nice if we had a way to delete all these when we were done for the day, or done with the order, or whenever you want. These could be PDF files, or text files if you're doing that, or whatever type of document you might be exporting.
Let's just make a button in here real quick, Design View. I'll make this smaller. Export PDF, and we'll put this in here, and we'll say Delete PDF. We'll do them all. We'll use a wildcard. So we'll assume any PDF files in your database folder are fair game to be deleted.
Right click, give it a name. I don't want to get yelled at. I always forget the name of the Delete PDF button. Don't forget your names. Alex will find you. He has a particular set of skills and he will find you.
Now, for this, we don't need to refresh. We just need to kill some stuff. But I am going to put an On Error Resume Next in here because if the file doesn't exist, the Kill command will throw up a VBA error. We try to avoid those.
The command is Kill and then the file name. That's pretty much it. So again, it's going to be CurrentProject.Path and a backslash. Now, you could put the exact same thing in here, for example, Order-whateverOrderID.PDF, if you want to just delete this one. But I'm going to delete all of them, any PDFs that are Order- something in this folder. We're going to put a star in there; that's the wildcard character, .PDF. That's it.
It's going to put in your error handling, it's going to issue that Kill statement, and then it'll do all the PDFs in that folder. Sometimes I put like a Beep at the end just so you know it's done. It did its thing.
Save it, Debug Compile, go back over, close this, open it up, and we'll hit Delete. I heard a beep and I just watched all those files go away. They just all disappeared off my desktop. It was like magic. They're all gone.
Now, you might not necessarily want this button here. This is like a cleanup at the end of the day. So let's do this: go into this button, take this code, cut that out, delete this routine.
Let's save this, come back out here. I just put the button here for ease of use while we're doing it. Save that.
Let's put this somewhere that is going to run when the database closes. Now, I like to leave my main menu open all the time. So when this thing closes, that indicates to me, hey, the database is shutting down. Let's put it in there. Design view, open this up, go to Events. Let's find either the OnUnload or OnClose event. They're different. OnClose is fine. Dot dot dot. Put it right in here.
I like to put a little note in here: Clean up delete temporary PDF files or whatever.
Throughout your workday, you're making PDFs, you're doing this, you're doing that. You might also want to delete whatever text files you get in there. Just make sure you don't put any PDF or text files in this folder that you care about. You could even create a temp folder under your database. You could do whatever you want. Of course, this isn't a file management class, but you get the point.
Save it. Let's go and close that. Open it back up again.
By the way, those close events will run whenever you switch from Form View to Design View as well, so that will also fire those off.
So, customer form will do the same thing again. Just create two of them. There's one. Now I'm seeing why I find it annoying that it puts the... let me go back and turn that off. OrderF. This little comma True here, let's put that to comma False. We'll just put a beep in here too because it's popping up on my other monitor. It's not my export one.
Open that up. Open that up. Let's go to you. We've got one for you. Let's open the next one for you. Beep. So we've got two of them now. Let me slide this over. There they are on my desktop. You can see them.
Close the database. I'll just close the main menu. When that happens, those guys go away. Isn't that cute? That's pretty cool.
Now, here's the problem. All those files we just deleted, they're gone. They're gone permanently. They do not go to the Recycle Bin. That's one of the problems with the Kill command in Visual Basic. It just deletes the file; it doesn't send it to the Windows Recycle Bin.
So, now, the example that I gave in today's class, you can just go regenerate that PDF file, no big deal. But I was recently working on a database project myself where I was creating some files that I wanted to delete sometimes. But once in a while, I might be like, yeah, I really needed that file, let me go back and get it.
So, if you're working with files that you sometimes want to delete, but in case you accidentally delete one that's important, you want to be able to go pull it out of the Recycle Bin. I'm going to show you how to do that in the extended cut. Unfortunately, Visual Basic by itself doesn't do that. So we're going to do a little PowerShell scripting. That will allow us to use the Recycle Bin. That's all covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. There's lots of them. There are hundreds of them. Gold members can download these databases that I build in the TechHelp videos, and you get access to the code vault where you'll find all this cool stuff right there at your fingertips. Check it out. Membership information down below.
If you want to learn more about working with files in Microsoft Access, I've got a couple of classes. Access Developer 30, I teach you basic classic VBA file input and output, reading and writing text files. Then in Developer 31, we go over lots more with VBA file and folder navigation, copying files, deleting files, compacting your backend, all kinds of stuff. In fact, this is the one where I cover the Dir function, GetAttr, all this stuff. Here's FileExists, FolderExists, creating folders, the length of a file, the Kill command, all kinds of cool stuff.
This stuff is great for working with temporary text files, especially or importing data that's in a nonstandard format. Lots of cool stuff in my Access Developer 30 and 31 classes.
There you go, folks. That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.
Quiz
Q1. What is the primary purpose of the VBA Kill command as described in the video?
A. To delete specified files from a folder
B. To move files to a different directory
C. To compress PDF files for emailing
D. To recover files from the Recycle Bin
Q2. Why should you be careful when using the VBA Kill command?
A. It can delete files even if they are open
B. It can delete any file specified, possibly including important ones
C. It always asks for confirmation before deleting
D. It copies files before deleting them
Q3. What happens if you try to use Kill on a file that does not exist?
A. Nothing happens, no errors occur
B. The command automatically creates the missing file
C. A VBA error is thrown unless error handling is used
D. The file is moved to a backup folder
Q4. What is the effect of deleting files with VBA Kill in terms of file recovery?
A. Files are moved to a backup folder
B. Files are sent to the Windows Recycle Bin
C. Files are permanently deleted and cannot be recovered from the Recycle Bin
D. Files can be restored with a simple Undo
Q5. How can you delete all PDF files in the database folder using the Kill command?
A. Use Kill with the full path to each specific PDF file
B. Use Kill with a wildcard pattern like *.PDF in the folder path
C. Use Kill with a folder name only
D. Use Kill without specifying any pattern or filename
Q6. When is a good time to run cleanup code to delete temporary files in a database, as suggested in the video?
A. Whenever a new file is created
B. When the main menu form closes or the database shuts down
C. Immediately after opening the database
D. Periodically throughout the day, every few minutes
Q7. What does the wildcard character (*) do in the context of the VBA Kill command?
A. It acts as a placeholder for any file extension
B. It allows deletion of multiple files that match the pattern
C. It prevents any files from being deleted
D. It restricts deletion to files created today
Q8. Which of the following statements is true regarding the use of CurrentProject.Path in the Kill command?
A. It retrieves the name of the currently opened report
B. It is used to specify the output format for PDF export
C. It gives the folder where the database file is located
D. It is not required to delete files in Access
Q9. What is the purpose of using Me.Refresh before exporting a report to PDF in the VBA code?
A. It reopens the entire database
B. It ensures any changes on the form are saved before export
C. It deletes old copies of reports
D. It closes all open reports
Q10. Why is adding error handling (like On Error Resume Next) recommended when using the Kill command?
A. To allow the deletion of locked files
B. To suppress errors when files do not exist, preventing VBA from stopping execution
C. To make sure files are moved to the Recycle Bin
D. To ensure a confirmation dialog always appears
Q11. Which event is used in the video to trigger file deletion automatically when closing the main menu form?
A. OnOpen
B. OnCurrent
C. OnClose
D. OnInsert
Q12. What is a limitation of the VBA Kill command discussed in the video?
A. It can only delete text files
B. It cannot delete files in subfolders
C. It permanently deletes files, not sending them to the Recycle Bin
D. It deletes only open files
Q13. If you want to make sure you do not lose important files, what is suggested in the video?
A. Never use the Kill command
B. Create a separate temp folder for temporary files
C. Always store important files as PDFs
D. Set the output file to the root C directory
Q14. If you want deleted files to be sent to the Recycle Bin instead of being permanently deleted, what is mentioned as a solution?
A. There is a built-in VBA keyword for that
B. Use PowerShell scripting along with VBA
C. Use Kill with a special flag
D. Use the Dir function instead
Q15. According to the video, in which classes does the speaker teach more about working with files and folders in Access VBA?
A. Access Developer 1 and 2
B. Access Intermediate and Advanced
C. Access Developer 30 and 31
D. Access Introduction and Reports
Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B; 11-C; 12-C; 13-B; 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 TechHelp tutorial from Access Learning Zone will cover how to use the VBA Kill command to delete files in Microsoft Access. Often, when working with a database, especially after exporting reports to PDF or generating temporary files like text or log files, you end up with a cluttered database folder. Today I'll show you a simple way to automate the cleanup process using VBA so you can get rid of those unwanted files when you are finished working.
The inspiration for this lesson comes from a question about exporting reports to PDF and the leftover PDF files filling up the folder. The question was whether there is an easy way to delete these files in bulk. Fortunately, VBA provides a straightforward solution with the Kill command. Just be careful when using it; the Kill command will delete any file you target, so always double-check your file paths and names. The good news is that Kill will not delete open files, such as your Access database, which adds a slight layer of safety.
If you have not watched my previous video on exporting reports to PDF, I recommend you do so first since I briefly reference the process here. That video also includes all the details on setting up the basics. For members, the extended cut also shows how to export a batch of PDF files at once.
In this lesson, I will use just a few lines of VBA code. If you are unfamiliar with adding VBA to your Access database, you should start with my Intro to VBA class, available for free on my website and YouTube channel. That class will teach you everything you need to know to get started.
Let me walk you through a typical scenario in my free TechHelp template database, which you can download from my website. In this database, customers can have orders, and each order can have an invoice. There is a button to preview invoices, but you might want to automate exporting invoices as PDFs to save time, especially if you want to email them or archive them digitally.
To automate this, you can create a button to export the report directly to PDF using VBA. The code requires you to save any changes on the form first with Me.Refresh, since reports pull their data from the tables, not directly from uncommitted form edits. Then you'll use the DoCmd.OutputTo command, specifying the report name, the PDF format, and the desired output file path and file name. I like to save these PDFs in the same folder as the database and use the order ID to make each file unique.
This setup allows you to quickly export various orders as PDFs with the click of a button. As you continue this process for multiple orders, your folder will start to accumulate a lot of these PDF files. That can make it messy, and you might want an easy way to clean them up at the end of the day or after sending them out.
To handle this, you can set up a button labeled "Delete PDF" that uses the VBA Kill command to delete all the PDF files matching a certain pattern, such as "Order-*.PDF", in the database folder. By using a wildcard, you can delete all files at once that fit that pattern. To avoid errors if some files are already missing, I recommend including "On Error Resume Next" in your code.
I like to give my controls meaningful names to keep things organized. You can implement your delete routine either as a button the user clicks when needed or, more seamlessly, as an automated task that runs when the database or main menu form closes. By placing the code in the OnClose event for your main menu, you ensure that all temporary PDF files are cleared out whenever you finish working for the day.
A word of caution: when you use the VBA Kill command, the files are gone permanently. They do not go into the Windows Recycle Bin, so there is no easy way to recover them. This is generally not a big problem with files you can easily recreate, but you need to be careful if there is any chance a file could be important.
For those cases where you want to be able to recover deleted files, I cover a PowerShell solution for sending deleted files to the Recycle Bin in the extended cut of this video. Visual Basic alone cannot do this, but combining VBA and PowerShell makes it possible. Members can access this extended tutorial, along with hundreds of others, on my website.
If you are interested in learning more about working with files in VBA, including functions like Dir and GetAttr, or methods for checking if a file or folder exists, I cover all these topics in detail in my Access Developer 30 and 31 classes. These courses will give you all the skills you need for file input/output operations, managing folders, copying and deleting files, and more.
That wraps up today's lesson on using the VBA Kill command to automate file cleanup in your Access database. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.
Topic List
Using the VBA Kill command to delete files
Exporting Access reports to PDF files with VBA
Building a dynamic file name for exported PDFs
Using CurrentProject.Path to specify output location
Creating a button to export reports as PDF
Adding On Error Resume Next for error handling
Using wildcards to delete multiple files at once
Deleting all PDF files in the database folder
Assigning code to button click events in Access forms
Moving file deletion code to a form's OnClose event
Automatic file cleanup when closing the main menu form
Warning about permanent deletion with Kill command
Ensuring Me.Refresh before exporting reports
Controlling output file preview with the OutputTo command
Handling multiple exported files in the folder
Limitations of Kill not sending files to Recycle Bin
|
