Open Folder
By Richard Rost
15 months ago
Add Button to Open Client Folders in Access Forms
In this Microsoft Access tutorial, I will show you how to create a button within your Access database's customer form that opens a specific folder for each client. You'll learn how to efficiently manage client-related files without storing them directly in your database. This involves using VBA to construct paths to client folders and employ the shell command to open these folders quickly. Additionally, we'll explore using the DIR function to check for the existence of folders and the MkDir command to create them if necessary. By the end, you'll have a streamlined process for managing client files directly from your Access interface.
Julian from Hoboken, New Jersey (a Platinum Member) asks: I know you're completely against storing files inside the database as attachments, so we're using a folder on the server for each client. How can I add a button in my Access database's customer form to quickly open a folder that's specific to each client? It would be super helpful if I could just click a button on the customer form to open the folder up without having to search for it every time. Is there an easy way to do this?
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
Recommended Courses
Keywords
TechHelp Access, open folder button VBA, client-specific folders, automate folder creation, file path variable, shell command for explorer, directory check VBA, make directory VBA, open client folder, Access customer form, VBA developer class, file I.O. functions, folder navigation VBA, TechHelp tutorial, Access database client files
Subscribe to Open Folder
Get notifications when this page is updated
Intro In this video, I will show you how to set up client-specific folders in Microsoft Access and add a button to your customer form that opens each client's folder directly from the database. We'll use VBA to generate file paths based on customer IDs, check if the folder exists using the Dir function, create it if missing with the MkDir command, and launch the folder in Windows Explorer with the Shell command. This tutorial is intended for users comfortable with VBA and is perfect for anyone managing client documents outside the database.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, I'm going to show you how to open a folder from your Access database, but more specifically, we're going to set up folders for each of our clients. That way you can click one button that will open up their folder, and you can drop whatever files and other things you want that are related to that client in that folder.
Today's question comes from Julian in Hoboken, New Jersey, one of my Platinum members. Julian says, "I know you're completely against storing files inside the database as attachments." Yes, I am very much so. "So we're using a folder on the server for each client. How can I add a button in my Access Database's customer form to quickly open a folder that's specific to each client? It would be super helpful if I could just click a button on the customer form to open a folder without having to search for it every time. Is there an easy way to do this?"
Well, of course there is. Let's see how to do it.
Before we get started, this is a developer-level class. What does that mean? Well, that means you're going to have to know some VBA. And if you don't know any VBA, go watch this video first. It's about 20 minutes long. It'll teach you everything you need to know to get started. Go watch this video on variables. And go watch this video on if-then statements. These are all free videos. They're on my website. They're on my YouTube channel. Go watch those and then come on back.
The first thing I'm going to do is set up a folder that we're going to pretend is my database folder. Normally, I just run these databases for the videos off my desktop. We'll just make a folder here real quick. No folder. We'll call this my database or whatever you want to call it. This will be your server folder. And in here, I'll drop my database, which I'm going to grab a copy of my TechHelp free template. There it is.
Now inside of this, wherever your server folder happens to be, if you've got a split database going on, this will be your back-end database file. In here, I'm going to also create another folder. We'll call it client files. So this is where my folder is. On your machine it might be different. It might be a Z-Drive or whatever. I'm going to copy that to my clipboard. In fact, let's do one further here. Let's go into the client files. Let's copy this whole thing to the clipboard. That's where your files are going to be.
Now, under client files, we'll have a folder for each customer. You'll have to make these for now, but we'll talk about this again in a few minutes. So let's say it's based on their customer ID. So another folder will make one for customer one. We'll make another new folder for customer two. We'll just do those two for now. So I got this whole thing database file, client files, and then one is where customer one's stuff will be.
Let's go back and open up the database now. So in here in the customer form, let's go to design view. Let's make a new button. So form design, grab a button, drop it over here, cancel the wizard. Then we'll make this the open client folder button. Resize it, make it look pretty however you want. Give it a good name so Alex doesn't yell at us open folder button, BTN. And then right-click build event that'll open up the code builder, mine's way over there. Let me resize it.
Okay, in here, I'm going to dim a variable called file path. That'll be a string. I'm going to set file path equals that thing that I just put on my clipboard. We're going to put a slash on the end of that. Then after that, we're going to add the customer ID. That's on the current form. Because we're on the customer form, I can use the customer ID. That's my file path. That's the folder that I want to open. Let's take a look at it, message box file path. Just want to see what it is at this point. Save it and then open it up. Let's close this and hit the button.
That's what it looks like. You can see users, Amicron, wonder, I have it in client files one. I'm going to put another trailing slash at the end of that too. Just so it's no question it's a folder. Now that we've got that file path all set, and again, this could be your server wherever you've got on the network that you're putting your files, up to your database folder.
Now to open that folder, we're going to use the shell command. You can use the shell command to open up other programs. You could say shell notepad.exe. And if it's not an application that's in your system's path, where it knows to find it, just put the full path in there. C colon, backslash, windows, whatever. The one that we're looking for is explorer.exe. Explorer is your file explorer. That's what Windows uses when you open up the folder to browse around. This guy, this is the file explorer. And this is explorer.exe. So I want to open up explorer.exe, but I want to open it with a parameter. I want to open it to the file path.
Now one more thing you need here is the windows style. The default is minimized, I believe. Let me see if I can get that out of here. I'm pretty sure it's minimized. Let's see. Comment. Yeah, minimized focus is the default. You don't want that. Otherwise, it'll be on your taskbar minimized. You want normal focus. They mean it's going to put it up in your face in a normal window. And then save it. Let's go back over here and hit the button. And there it is. Look at that. It opened it up to my database client files, the one folder. See that? Let's go to the next customer. Customer ID to open her up. And now we're in folder two.
Now what happens if we try to open up folder three? See now it's in Richard personal documents. If it can't find that folder, it's going to open it up to your documents folder. Wherever your documents folder happens to be. So it'd be nice if we could figure out if that folder exists and if not create it. And we can do that with a little magic something called the DER function. It stands for directory. Check to see if it exists. And it looks like this. If DER file path, VB directory. In other words, we're looking for a directory. Now if you say DER that folder, VB directory. If that exists, it'll return the name of the directory. If not, it'll return an empty string. So if it's an empty string, then we're going to create it. Right folder doesn't exist. Create it. And we can create a folder with the make DER command. We're just going to say make DER file path. Now this assumes that the parent folder exists or you won't be able to make it. All right, I'll just throw an error. And then end if.
So now we're checking to see if that folder exists. If not, we're going to create it. And then when we're all done either way, we're going to open that folder up. Save it. Let's come back here and hit the button. And boom, I'm in folder three. It didn't find it and it created it for me. Let's go to another one. Go to regional broccoli, customer nine. Go to this folder. Boom. Client file. Client folder nine. And if you go up the client files, you'll see there's one, two, three, and nine. So it'll create them as it needs them. And that's not that hard. What was that? I was like, no, one, two, three, four, five lines of code.
These are all called file I.O functions. Directory, make directory, even shell. I covered this in my developer file classes. I've got several classes on this topic starting with developer 30. Classic VB file input output. Reading and writing text files. Developer 31 is file and folder navigation, copying files, compacting your Access database back end files automatically. That's pretty cool. And lots more stuff. I've got several other lessons all on file input and output working with files and folders, doing all kinds of crazy stuff. So if you like that kind of thing, check it out, put links down below.
But that is going to be your tech help video for today. Tomorrow, I'm going to teach you to figure out if that folder is already open. Because right now, if you open a folder, and let's say you minimize it and move it out of the way, hit the button again, it's going to open up another copy, and then another copy, and then another copy. So tomorrow, I'm going to teach you to say, hey, if that folder is already open, just switch to it. Instead of opening up another copy of it.
But that's going to do it for today, folks. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow.
TOPICS:
How to open a folder from Access database
Setting up client-specific folders
Adding a button to the customer form
Using VBA to handle file paths
Implementing the shell command to open folders
Checking if a folder exists using DER function
Creating a folder with the MakeDir command
Folder creation based on customer ID
Handling non-existent folders in Access
Navigating file explorer programmatically
COMMERCIAL:
In today's video, we're going to learn how to set up folders for each of your clients in an Access database and create a button to open them swiftly. You'll discover how to use VBA to automate folder creation and utilize the shell command to open client folders with ease. We're also checking if a folder exists and creating it if it's missing. This is a developer-level class, so a basic understanding of VBA is recommended. For more details on manipulating files and directories with Access, be sure to check out my developer classes. 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 main goal of the tutorial presented in the video?
A. To store client files directly within the Access database
B. To create a report listing all client files
C. To set up a button in the Access database that opens a client-specific folder
D. To learn how to create a split database
Q2. Why does Richard recommend not storing files directly inside the Access database?
A. It requires complex VBA coding
B. It can drastically increase the size of the database, potentially leading to performance issues
C. It is more secure to store files within the database
D. Access does not support file attachments at all
Q3. What prior knowledge does Richard suggest you have before watching this developer-level video?
A. SQL and Macros
B. VBA, specifically knowledge about variables and if-then statements
C. Advanced Excel and PivotTables
D. Basic HTML and CSS
Q4. Which VBA function is used in the video to determine if a folder exists?
A. MakeDir
B. File.Exists
C. CheckPath
D. Dir
Q5. What happens by default if you try to open a non-existent folder following the tutorial's method?
A. The system displays an error message
B. The shell command automatically creates the folder
C. Windows Explorer opens the user's Documents folder
D. Access crashes immediately
Q6. What command is used to create a new folder if it does not already exist?
A. CreateFolder
B. MakeDir
C. NewDir
D. buildFolder
Q7. What does the shell command 'explorer.exe' do in the context of this tutorial?
A. Closes all open folders
B. Opens the Command Prompt
C. Opens Windows Explorer to a specific path
D. Launches Internet Explorer
Q8. What additional feature related to folder opening is Richard planning to cover in the next video?
A. How to copy files into the opened folder
B. How to open multiple folders at once
C. How to switch to an already open folder instead of opening another copy
D. How to close the folder automatically after a few minutes
Answers: 1-C; 2-B; 3-B; 4-D; 5-C; 6-B; 7-C; 8-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 AccessLearningZone.com focuses on how to open a folder for each client directly from your Access database. I'm Richard Rost, your instructor, and I'll guide you through setting up a system where you can click a button within your Access database to open a client's folder, eliminating the need to search for it each time.
Julian, a Platinum member from Hoboken, New Jersey, raised an important question about incorporating a button in an Access Database's customer form to quickly open a specific client's folder. He mentioned that while he's aware I advise against storing files within the database as attachments, he aims to use a server folder for each client instead. Julian wanted an efficient method to access these folders, and I'm here to show you just how simple it can be.
This tutorial is tailored for those acquainted with VBA, as it involves some coding. If you're new to it, I recommend starting with some basic tutorials on variables and if-then statements, all available for free on my website or YouTube channel.
To begin, let's create a folder that will act as our database directory. Normally, I develop databases on my desktop for video purposes. We'll quickly set up a folder structure. Suppose your database resides in a server folder and is split into a front-end and back-end. Inside the main folder, we'll create another called "client files" where you'll keep folders for each customer. Depending on your server setup, the path might differ, such as a Z-Drive, so you'll need to adjust accordingly.
Within "client files," we'll establish folders based on customer IDs, like customer one and customer two. Now, you have a clear path structure: the main folder branches into client files and then into each customer's folder.
Next, I'll demonstrate how to integrate this feature into your Access database. Enter your database's customer form in design view, add a button labeled "Open Client Folder," and modify it to meet your aesthetic preferences. Upon clicking this button, it should trigger a code that retrieves the designated client folder using a constructed file path, which combines the main clipboard path and customer ID from the current form.
The command we employ is the Shell command, frequently used to open programs. In this case, it opens the file explorer (explorer.exe) with the specified folder path, ensuring it opens in a normal window. If this folder doesn't exist when you attempt access, our VBA code can use the Dir function to check and create the directory via the MkDir command.
After setting up your file path and considering if the folder already exists, you can test by clicking the button to confirm the folder opens as expected. If the folder doesn't exist yet, the code will create it, ensuring seamless navigation between clients' folders.
These concepts fall under file input/output (I/O) functions in VBA, which also cover operations like directory checks, file navigation, and database management. If you're intrigued by file I/O functions, I offer courses starting with Developer 30, which delve deeper into these methods.
This session showcases the ability to automate folder access efficiently. Next, I'll address how to recognize opened folders to prevent duplicate opening, which can clutter your workspace.
I'm glad you joined this tutorial; I hope it was educational. For comprehensive, step-by-step instructions, visit my website. Live long and prosper, my friends.Topic List How to open a folder from Access database
Setting up client-specific folders
Adding a button to the customer form
Using VBA to handle file paths
Implementing the shell command to open folders
Checking if a folder exists using DER function
Creating a folder with the MakeDir command
Folder creation based on customer ID
Handling non-existent folders in Access
Navigating file explorer programmatically |
