Dir Function
By Richard Rost
39 days ago
Check If File Exists Using Dir Function in Access VBA In this video, we will walk through how to use the DIR function in Access VBA to check if a file exists before attempting to open it. I demonstrate how to update a customer form to include a button that opens a resume file, and then show how to add code that uses the DIR function to verify the file's existence and display a clear message if it is missing, helping prevent confusing errors for your users. Error handling alternatives and why not to store files directly in your database are also briefly discussed. Jasper from Cambridge, England (a Platinum Member) asks: I've got some VBA code that's supposed to open a file from a folder on my computer, but sometimes the file isn't there anymore. When that happens, Access throws an error and the code stops. Is there a simple way in VBA to check if a file exists before trying to open it? MembersThere 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, DIR function, file exists, VBA, check file existence, error handling, FollowHyperlink, Shell function, file path, double double quotes, file IO, looping through files, open file with Notepad, Message Box file not found, copy file path Windows
Subscribe to Dir Function
Get notifications when this page is updated
Intro In this video, we will walk through how to use the DIR function in Access VBA to check if a file exists before attempting to open it. I demonstrate how to update a customer form to include a button that opens a resume file, and then show how to add code that uses the DIR function to verify the file's existence and display a clear message if it is missing, helping prevent confusing errors for your users. Error handling alternatives and why not to store files directly in your database are also briefly discussed.Transcript Ever had your Access database try to open a file that isn't there and suddenly you get some weird, random error popping up? Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we're going to talk about using the DIR function in Access VBA to check whether a file actually exists or not before you try to open it. This way your database can handle missing files gracefully instead of surprising your users with confusing errors.
Today's question comes from Jasper in Cambridge, England, one of my Platinum members. He says, "I've got some VBA code that's supposed to open a file from a folder on my computer, but sometimes the file isn't there anymore. When that happens, Access throws an error and the code stops. Is there a simple way in VBA to check if a file exists before trying to open it?"
Yes, Jasper, the easiest way to do this is with the DIR function. DIR is short for directory. It's just like in the Windows command prompt or the DOS command prompt if you're old like me and you remember DOS. At the command prompt, you type in DIR and it gives you a list of what's in there. We can do the same thing in VBA to see if a file exists.
Now, since we are using VBA, if you've never done any VBA programming before, go watch this video. It's about 20 minutes long; it'll teach you everything you need to know to get started.
There are a couple of ways to open files and folders from Access. You can either use FollowHyperlink or you can use the Shell function. There's lots of different ways. I'll put links to both of these videos down below. You can check those out.
Here I am in my TechHelp free template. This is a free database you can grab a copy of from my website if you want to.
Let's say in the customer table, I've got a field in here where I store their resume, so "Resume File." Now, we don't store files in our databases. We learned that before in lots of my previous videos. We store a link to where the file is located. You set up a folder, you put all your files in that folder, and you put the path and file name here in the database. That's the only way we do it. We don't use attachments; we don't store files in our databases. I have whole separate videos on why you don't do that. I'll put a link down below.
I'm going to save that and close it. Let's go into my customer form, and I'm just going to cheat here. I'm just going to take this country field and rename that to "Resume." I'm going to open this up and change this to "Resume," and there we go, so now "Country" is now the "Resume File."
I'll need a button to open it or you can use a double-click event. Either way, buttons are fine. Copy, paste the button, we'll stick it down here, and we'll change this to say "Open." We'll double-click on this guy, we'll rename it to "OpenResumeButton" (btn), and then I'm going to right-click and "Build Event," and now I'm in my code editor.
Yes, I'm using dark mode now if you missed the last couple of videos. You can use FollowHyperlink if you want. I tend to like to use Shell or ShellExec, so I'm just going to use Shell, and I'm going to assume for now that these are all text files, so we're going to be opening up just a text file. We use Notepad, right? So "notepad.exe" should be a new Windows path, and then we want to enclose our full path and file name in double quotes. We have to put double double quotes inside the string and the resume file name and close up those double quotes.
Now this whole thing is, you know, C:\whatever\whatever\document.txt, and it's inside of double quotes inside the string. If you're not familiar with double double quotes, I have a whole separate video on it. I'll put a link down below. There are videos on all this stuff.
After this, we need a comma, and then we need a vbNormalFocus so we can actually see it and that'll do it. Save it, Debug > Compile once in a while. I'm going to close this and let's close this form and reopen it.
Now we need to put a path in here. I've got a file on my desktop, "Resume File.txt." You could put it wherever you want to put it. Put it in a folder somewhere; we're not going to lose it. I'm going to right-click on this guy and go "Copy as Path." That copies the whole path and file name to your clipboard. That's a pretty cool trick. I'm not sure when they added it, Windows 10 or 11. Now I'm going to paste that in here, but make sure you get rid of the double quotes. It puts double quotes on it. We don't want those in the file field.
Now when I hit "Open," there's my resume file. It opens right up.
Let's say this file goes bye-bye. I'm going to just rename it so the database can't find it anyway. I'll just change the file. It's still there; it moves around. Now if I hit the button, I get - oh, you can't see it on the other screen. Hold on, there it is - it says can't find it. "Do I create a new file?" No, I don't want to. No, I don't want a new file. I don't want this to happen. I want it to say, "Hey, the file is not there."
So how do we do that? We can use the DIR function - DIR, right, directory function. Let's go back into the code. Let's go back into our button, right-click, build event.
Right here, before we get to the shell command, we're going to say:
If DIR (now in DIR, you put what file you're looking for, and that file happens to be the resume file) - if DIR is able to locate the file, it returns the file name. If not, it returns an empty string.
So I'm going to say equals empty string, and the reason why is because you can use DIR for looping through the files in a folder, and it will return each one successfully. That's a whole separate video, but for now just know that if that file doesn't exist, you get an empty string. If it equals the empty string, then Message Box "File not found" exits sub, and if not, okay.
Debug > Compile. Debug > Compile. Oh, not Run. I do that a lot. I accidentally slide over here to "Run." That just means it's trying to run. You can specify what you want to run, a macro. I don't want to run a macro. Debug > Compile. There we go.
Close it, close it, save it, open it, and now I'll hit Open and it says "File not found." It's a nice, graceful error. Your users aren't panicking because they don't know what's going on. Pretty straightforward, pretty simple.
You could have also handled this instead of using the DIR command, you could have also handled this with some error handling, but when you launch Shell, it's going to open Notepad either way, and Notepad is what's actually generating the error there. So in this case, it's nice to use the DIR function ahead of time.
If you want to learn about why we don't store files inside our database, text files, images, whatever, go watch this video. If you want to learn more about those double double quotes (those are a little tricky, those trip a lot of people up when you want to put quotes inside a string, you have to put double double quotes), go watch this video. It explains it in a lot more detail.
If you want to learn more about file IO and that DIR function and looping through files in a folder, go watch my Access Developer 31 course. I cover all that in great detail. You learn how to navigate files on a folder, copy files, compact your back end in code, all kinds of stuff, but we start covering things like the DIR function in this class.
Today you learned how to use the DIR function to check if a file actually exists before your database tries to open it, which helps you avoid those confusing errors when a file isn't where you expect it to be.
Post a comment down below. Let me know how you liked today's video and how you plan to use this in your database.
That's going to be your TechHelp video for today brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of using the DIR function in Access VBA as described in the video?
A. To delete files from a folder
B. To check if a file exists before opening it
C. To change file permissions
D. To compress database files
Q2. What does the DIR function return if the specified file is not found?
A. An error code
B. The file's full path
C. An empty string
D. The folder name
Q3. According to the video, what is the recommended way to store references to files like resumes in an Access database?
A. Store the files as attachments within tables
B. Store a link or path to the file in the database
C. Write the contents of the file into a memo field
D. Use an external application to manage file storage
Q4. What happens if you attempt to use the Shell function to open a file that does not exist without using the DIR function to check first?
A. The file gets created automatically
B. Access will display a custom error message
C. No action occurs
D. An unexpected error from Notepad (or the called program) may appear
Q5. Which VBA command is used in the example to alert users if the file is not found?
A. Print
B. Debug.Print
C. MessageBox
D. MsgBox
Q6. Why is it recommended not to store files directly inside an Access database?
A. It increases security risks
B. Databases cannot handle large amounts of text
C. It can cause bloat and performance issues
D. Files are easier to edit within the database
Q7. If DIR returns an empty string when searching for a file path, what should your code do according to the video?
A. Proceed with opening the file
B. Notify the user and exit the subroutine
C. Try to create a new file automatically
D. Search for other files in the folder
Q8. Which function, in addition to Shell, was mentioned in the video as a way to open files in Access VBA?
A. OpenFile
B. CreateObject
C. FollowHyperlink
D. RunMacro
Q9. Why is using error handling alone not as effective as checking file existence with DIR before using Shell, as discussed in the video?
A. Error handling is outdated
B. The error is generated by the called program, not VBA
C. Error handling cannot detect missing files
D. It causes Access to close
Q10. What visual tool was used in the database form example to trigger the file opening event?
A. Combo box
B. Label
C. Button
D. Text box
Answers: 1-B; 2-C; 3-B; 4-D; 5-D; 6-C; 7-B; 8-C; 9-B; 10-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 covers how to prevent random VBA errors in Microsoft Access that occur when your database tries to open a file that is missing. Many of us have run into this situation where a file path is stored in the database, but the file itself gets moved or deleted, resulting in an unhelpful error message appearing for your users.
To address this, I demonstrate how you can use the DIR function in Access VBA to check whether the file actually exists before attempting to open it. By doing this, you can inform your users if the file is missing, rather than letting Access surprise them with a confusing error.
A common question I receive relates to this exact issue. Users often set up VBA code to open a file stored somewhere on their computer, but sometimes that file is gone by the time they try to access it. When that happens without any checks in place, Access stops the code execution and throws an error. The simplest way to prevent this scenario is by using the DIR function. DIR allows you to check if a specific file exists, much like using the directory command in the Windows or DOS command prompt. If DIR finds the file, it returns the file name as a string. If not, it returns an empty string.
Before diving in, if you're new to VBA, I recommend checking out my beginner's video to get comfortable with the basics of programming in Access VBA.
When it comes to opening files from Access, you have several options. The FollowHyperlink method and the Shell function are two common techniques, each with its own use cases. For example, you might prefer using Shell if you're opening text files with Notepad, in which case you specify the application's path and the file you want to open.
In the demonstration, I use the TechHelp free template, which you can download from my website. In the customer table, instead of storing documents as attachments, I store only the path to the file (such as a resume) in a text field. This is best practice for Access and helps avoid database bloat and other complications. For more on why you should not store files directly inside your database, I have separate videos available.
To allow users to open the saved resume file, I set up a button on the customer form. The button is programmed with VBA code that uses the Shell function to open the file, assuming it is a text file. However, if the file has been moved or deleted, we need to check for its existence first.
This is where the DIR function comes into play. Before running the Shell command, I insert a check using DIR against the file path in the database. If DIR returns an empty string, that means the file doesn't exist, so I show a message box alerting the user and stop the code execution. If the file is found, the code proceeds to open the file as usual.
It's important to compile your code frequently in the VBA editor and to save your work as you proceed. Also, be careful when copying the path of a file in Windows, as newer versions sometimes add extra double quotes which you should remove before storing the path in your database.
Using this approach provides a much cleaner and more user-friendly experience, preventing Access from throwing generic errors that confuse users. You could also handle missing files with standard error handling, but since launching Shell essentially hands off the process to an external application (like Notepad), using DIR beforehand allows you to control the user experience within Access.
If you're interested in learning more about how DIR can be used for other tasks, such as looping through files in a folder, or if you want a deeper understanding of handling file input and output using VBA, take a look at my Access Developer 31 course. There you will learn how to navigate folders, manage files, and write more advanced VBA code.
By using DIR to verify file existence, you can make your Access applications more robust and ensure your users are kept informed rather than confused by missing files.
Be sure to leave a comment to share your thoughts on today's topic or let me know how you plan to apply this technique in your own projects.
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 Using the DIR function to check for file existence in VBA
Adding a resume file path field to an Access table
Updating a form control to reference the resume file
Creating a command button to open a file in Access
Writing VBA code to open a file using the Shell function
Formatting file paths with double quotes in VBA strings
Copying the full file path using "Copy as Path" in Windows
Handling missing files with a message box in VBA
Using an If statement with DIR to check if a file exists
Exiting a subroutine gracefully when a file is not foundArticle When working with Microsoft Access databases, it is common to store links to files such as resumes or documents rather than storing the files directly inside your database. In practice, your database might have a field containing the path and filename for each customer's document. When you want to open one of these files, problems can arise if the file has been moved or deleted since the link was saved. If you try to open a file that is no longer present, Access will usually throw a confusing error, which can be frustrating for both you and your users.
To prevent this, you can use VBA to check whether a file actually exists before trying to open it. One of the most straightforward ways to do this in VBA is with the DIR function. The DIR function returns the name of a file or folder that matches a specified pattern. If the file does not exist, DIR simply returns an empty string. This makes it easy to check for the presence of a file before attempting any further actions.
Let us look at a practical example. Suppose you have a table in your Access database where each record includes a field containing the path to a document, such as a customer's resume. Your form might have a textbox bound to this field and a button labeled "Open" for the user to click if they want to open the associated file.
You can attach VBA code to this button's click event. The code should first verify that the specified file exists using the DIR function. If the file is missing, you can display a friendly message to the user instead of letting Access show a cryptic error. If the file exists, you can proceed to open it.
Here's what the code might look like. Imagine you have a control named Resume on your form, which holds the full path to the file you want to open. The button is named btnOpenResume.
Open the form in Design View, right-click the button, and choose Build Event to open the VBA editor. Then enter the following code:
Private Sub btnOpenResume_Click()
Dim FileName As String
FileName = Me.Resume
If DIR(FileName) = "" Then
MsgBox "File not found", vbExclamation
Exit Sub
End If
Shell "notepad.exe """ & FileName & """", vbNormalFocus
End Sub
Let us break down what is happening. The code first retrieves the path from the Resume field and assigns it to the FileName variable. Next, it uses the DIR function to check if a file with that name exists. If DIR returns an empty string, meaning the file was not found, a message box notifies the user and the subroutine exits without trying to open the file. If the file does exist, the code uses the Shell function to launch Notepad and open the specified file. Note that you enclose the file name in double quotes within the Shell command to ensure that paths containing spaces work correctly.
This logic prevents your users from running into unexpected errors if a file has been deleted or moved. Instead, they get a clear, user-friendly message explaining that the file was not found. This approach is flexible. You can easily adapt the code to work with other file types and applications by changing "notepad.exe" to another application, or use Application.FollowHyperlink to open documents with their associated programs.
It is always good practice when handling file paths in your database to check whether the file still exists before allowing any operations on it. The DIR function is a simple and efficient way to do this in VBA, saving both you and your users from confusion and unnecessary errors.
To summarize, using the DIR function in your VBA code enables you to verify the existence of files before trying to open them, which makes your Access applications more robust and user-friendly. Whenever you store a file path in your database and provide a way for users to open files, always check with DIR first to ensure a smooth experience. |
