Introduction

Welcome! File System Object: Drives & Folders

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 35. In this course we will continue our work with the File System Object, covering how to list and check drive properties, verify if drives exist, understand GetDrive versus GetDriveName, and check free space. We will also discuss folder navigation, getting parent folders, generating temporary file names, and accessing special Windows folders. Finally, we will talk about the Not In List event, how to let users add items to combo and list boxes that are not already present, and discuss using the ListItemsEditForm for this purpose.

Navigation

Prev: D35
Next: Lesson 01

Keywords

Access Developer, file system object, file I/O, get drive properties, check drive exists, get free space, get drive name, parent folder name, generate temp file name, special folders, not in list event, combo box add item, listitemseditform

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 35 brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Today's lesson is going to continue our look at the File System Object, Parts 6 and 7.

Well, it is not completely over. There are going to be little bits and pieces. I will be adding in future lessons. But yeah, most of it, yes.

We will be talking about the On Not In List event, how to add items to a list box or a combo box when it is not in the list. What do you do about that?

This class follows Access Developer 34. I strongly recommend that you have taken 30 through 34. Those are all the file I/O classes if you want to continue with the file I/O. And of course, I recommend you do not skip levels at all. So you should have taken all the previous developer, advanced, expert, and beginner lessons. For more information on why, read that page.

I will be using Microsoft Access 365. It is currently 2022. So this is roughly equivalent to Access 2019. If you have questions, just scroll to the bottom of the page that you are watching this video on and post them right there.

If you have questions that do not pertain to this specific class, you can post them in the Access forum.

Now let us take a look at what exactly is covered in today's class.

In Lesson One, we are continuing with the File System Object. This is Part 6. We will begin by getting a list of drives and then going through all the drive properties, the total space, the space free, the serial number of the drive, and lots more. Then we will check to see if a drive exists. That is handy to check to see if perhaps the network drive that the database is on is actually connected or not. Then we will learn the difference between GetDrive and GetDriveName, then why it is important. Then we will see how to check the free space on the database drive. So you can say, okay, my database drive is drive Z. How much space is free on it? If it is getting low, maybe give a warning message, that kind of stuff. That is all covered in Lesson One.

In Lesson Two, we are continuing and concluding with the File System Object, Part 7. We will see how to get the parent folder name of the folder that you are in, how to go up one folder, and we will make an up one folder button. Now, we will stop at the root. We will get the absolute path name, determine the current folder that we are in. We will learn about GetFile versus GetFileName, just like GetDrive versus GetDriveName. We will see how to generate temporary file names. We will learn how to get the special folders, the Windows system temp folders, those kinds of things. All that is coming up in Lesson Two.

In Lesson Three, we are going to learn about the Not In List event. What to do when your user wants to add something to a combo box or a list box that is not in the list. There are a couple different things you can do. You can either use the Not In List event and add it programmatically, or you can set up something called a ListItemsEditForm, which we did look at in Access Beginner 8. We are going to talk a lot more about it today.

Quiz

Q1. What is the main focus of Lesson One in this video?
A. Working with the File System Object, focusing specifically on drives and their properties
B. Creating new tables in Access
C. Learning about relational database design
D. Setting up user security in Access

Q2. Which property can you check using the File System Object to determine if a drive is running out of space?
A. DriveType
B. SerialNumber
C. FreeSpace
D. CreationDate

Q3. What is the purpose of checking if a drive exists in your Access database?
A. To decide which user can access the database
B. To determine if a network drive is connected and accessible
C. To automatically create new folders
D. To manage user permissions

Q4. What is the difference between GetDrive and GetDriveName?
A. GetDrive returns only the drive letter, GetDriveName returns the drive properties
B. GetDrive returns the drive object, GetDriveName returns the drive letter or path name
C. GetDrive creates a new drive, GetDriveName deletes the drive
D. GetDrive is for network drives, GetDriveName is for local drives

Q5. Why might you warn a user if the space on their database drive is low?
A. To prevent unauthorized access
B. To make sure there is enough space for the database to operate properly
C. To reduce network traffic
D. To speed up data imports

Q6. What topic is the focus of Lesson Two?
A. Creating tables and queries in Access
B. Getting parent folder names and generating temporary file names using the File System Object
C. Setting up user-level security in Access
D. Importing Excel files into Access

Q7. What does the "up one folder" button do in the context of this lesson?
A. Deletes the current folder
B. Renames the current folder
C. Navigates to the parent folder
D. Copies files into the folder

Q8. Why would you want to get special folders like the Windows system temp directory?
A. To store temporary files created by your Access application
B. To back up your entire database
C. To archive historical data
D. To export records to Excel

Q9. What is the main topic of Lesson Three in the video?
A. Linking Access with SQL Server
B. Handling the Not In List event for combo boxes and list boxes
C. Setting up password protection
D. Automating email notifications

Q10. What is one method discussed to allow users to add an item not found in a combo box list?
A. Allow users to add items only via the table directly
B. Use the Not In List event to add the item programmatically
C. Require users to submit a request to the database administrator
D. Use a macro to delete the item from the list

Q11. What is the alternative to handling the Not In List event directly in code?
A. Hide the combo box from users
B. Use the ListItemsEditForm property to provide a form for editing list items
C. Only allow selection from fixed lists
D. Require users to restart the database

Answers: 1-A; 2-C; 3-B; 4-B; 5-B; 6-B; 7-C; 8-A; 9-B; 10-B; 11-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 video from Access Learning Zone continues our exploration of the File System Object as part of the Access Developer Level 35 course. I am Richard Rost, your instructor. In this lesson, I will walk you through Parts 6 and 7 of working with the File System Object in Microsoft Access. While we are nearing the end of this particular series, I will still be revisiting bits and pieces in future lessons where necessary.

A key focus today is on the On Not In List event and how to handle situations where a user tries to add something to a combo box or list box that is not already in your list. We will talk about the different approaches you can take in this situation.

This course builds on what was covered in Access Developer 34. If you have not already taken Developer 30 through 34, I highly recommend doing so, especially if you are interested in file input and output in Access. In fact, my advice is always to follow the lessons sequentially without skipping any levels, starting with the beginner, expert, advanced, and developer series. There are good reasons for this, which you can read about on my website.

For these lessons, I am using Microsoft Access 365, which, at the time of recording in 2022, is comparable to Access 2019. If any questions come up as you are working through the material, please post them right on the page where you are watching the video. For any other Access related questions not specific to this lesson, use the Access forum on my site.

Now, let me break down what we will cover in today's class. Lesson One continues our work with the File System Object in Part 6. We will learn how to get a list of all drives on your system and examine their properties. This includes looking at the total space on each drive, how much free space remains, serial numbers, and a variety of other details. I will show you how to check if a particular drive exists, which is especially useful for verifying whether a network drive is currently connected. We will also discuss the difference between GetDrive and GetDriveName and why understanding this distinction matters. You will see how to check the available space on the drive where your database is stored, so you can alert the user if free space is running low.

Moving on to Lesson Two, we will continue and wrap up our overview of the File System Object with Part 7. Here, we will find out how to retrieve the parent folder of your current directory and implement a button to move up one folder in your interface. We will look at how to stop navigation at the root folder, get the absolute path of your current location, and understand the differences between GetFile and GetFileName as we did before with drives. You will also learn how to generate temporary file names and identify special folders like the Windows system temp folder.

In Lesson Three, I will cover handling the Not In List event. This is important when your users want to add something new to a combo box or list box that is not present in your pre-defined list. There are a few strategies for managing this. You can handle the Not In List event programmatically to add the new item, or you can use a ListItemsEditForm, a tool we first looked at back in Access Beginner 8. In this lesson, we will take a closer look at how this works and how it can help you make your database more user-friendly.

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

Getting a list of drives with the File System Object
Examining drive properties like total and free space
Retrieving the drive serial number
Checking if a drive exists (e.g., network drives)
Using GetDrive versus GetDriveName
Checking free space on the database drive
Displaying a low disk space warning
Getting the parent folder name
Navigating up one folder in a directory structure
Creating an "Up One Folder" button
Detecting and stopping at the root directory
Obtaining the absolute path name
Determining the current folder
Using GetFile versus GetFileName
Generating temporary file names
Retrieving special folders like Windows temp
Handling the Not In List event for combo boxes
Adding new items to a combo box programmatically
Setting up a ListItemsEditForm for combo boxes

Article

Today, we are going to continue our exploration of the File System Object in Microsoft Access, and also discuss the On Not In List event. This lesson is designed for developers who already have some experience with Access and are ready to work with more advanced file input/output operations and form events. If you have not yet worked through the previous material on file I/O, I strongly recommend you review those basics first. We will be using Microsoft Access 365, but these techniques are also applicable to Access 2019.

Let us start with the File System Object, focusing on working with drives. The File System Object, often abbreviated as FSO, offers a lot of useful functions to interact with the file system directly from Access using VBA. Suppose you need to get a list of the drives on your machine or network. This can help your application check if a mapped network drive is available before trying to access files on it.

First, you will want to create a FileSystemObject in your VBA code. You can do this using late binding or by referencing the Microsoft Scripting Runtime library. To create the object, use the following VBA code:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Once you have your FSO object, you can enumerate all the drives like this:

Dim d As Object
For Each d In fso.Drives
Debug.Print d.DriveLetter & " " & d.TotalSize & " " & d.FreeSpace
Next d

This loop will print out the drive letter, the total size of the drive, and the amount of free space. You can retrieve other properties as well, such as d.SerialNumber, d.VolumeName, d.DriveType, and d.IsReady. For example, checking d.IsReady is important if you are working with removable or network drives, as trying to access them when they are not available will result in errors.

If you want to specifically check if a drive exists, perhaps to confirm that a network share is connected, you can use the DriveExists method:

If fso.DriveExists("Z:") Then
MsgBox "Drive Z exists!"
Else
MsgBox "Drive Z is not available."
End If

Another useful distinction is between GetDrive and GetDriveName. The GetDrive method returns a Drive object representing the drive, allowing you to examine its properties. For example:

Dim driveZ As Object
Set driveZ = fso.GetDrive("Z:")
Debug.Print driveZ.DriveLetter, driveZ.FreeSpace

GetDriveName on the other hand just returns the drive name as a string, such as "C:\" or "Z:\". This is useful when you just need the textual representation rather than all the properties.

Checking free space is also handy for applications that store lots of data or attachments. You might want to warn your users if the database drive is running low on storage. Here is an example that checks the space and shows a simple warning:

If driveZ.FreeSpace < 1000000000 Then
MsgBox "Warning: Less than 1 GB free on drive Z:"
End If

In addition to drives, you may need to manipulate folder paths. For example, if you have a file path and you want to get the parent folder, you can use the GetParentFolderName method:

Dim folderPath As String
folderPath = "C:\Data\MyFile.txt"
Debug.Print fso.GetParentFolderName(folderPath)

If you want to move up one folder in your code, you could create a button on your form labeled "Up One Folder," and in its click event use a similar approach. Just make sure you stop at the root folder so you do not end up with errors.

To get the absolute or full path name of a folder or file, use the GetAbsolutePathName method:

Debug.Print fso.GetAbsolutePathName("MyFile.txt")

This will return the full path, such as "C:\CurrentFolder\MyFile.txt".

Understanding the difference between methods like GetFile and GetFileName is also important. GetFile returns a File object that exposes properties such as size, attributes, and creation date. GetFileName simply returns the name portion of the file path as a string.

Generating temporary file names is another useful trick, especially if you are working with exports or temporary data. The GetTempName method creates a unique temporary file name for you:

Dim tempFile As String
tempFile = fso.GetTempName
Debug.Print tempFile

If you want to know where the Windows system temp folder is, you can use the SpecialFolders collection:

Dim tempFolder As String
tempFolder = fso.GetSpecialFolder(2) ' 2 is the TemporaryFolder
Debug.Print tempFolder

Now, let us discuss the Not In List event. When working with combo boxes or list boxes in Access, sometimes the user may want to enter a value that is not currently available in the list. Instead of showing an error, you can handle this situation by responding to the Not In List event. Here is how you can do it:

Suppose you have a combo box named "cmbCategory". In the properties of this combo box, set the Limit To List property to Yes. Then, in the form's code, add an event handler for Not In List:

Private Sub cmbCategory_NotInList(NewData As String, Response As Integer)
If MsgBox("Do you want to add '" & NewData & "' to the list?", vbYesNo) = vbYes Then
' Add the new value to the underlying table
CurrentDb.Execute "INSERT INTO Categories (CategoryName) VALUES ('" & Replace(NewData, "'", "''") & "')"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

This code prompts the user to confirm adding the new value. If they click Yes, the value is added to the underlying Categories table, and Access will automatically requery the combo box. If they click No, they can try again.

Another approach is to use the ListItemsEditForm property. This lets you specify a form that will open so the user can edit the items in the list, instead of simply adding them from the combo box. This method is more appropriate when users need to fill out additional information besides just the new item name.

Setting up ListItemsEditForm involves creating a form for editing the items, and then setting this property on the combo box. When the user chooses to add a new item, Access opens the form for them to enter the details.

Both of these techniques provide a more user-friendly way to manage data in list controls, letting users expand the list as needed without running into frustrating roadblocks.

In summary, the File System Object gives you the ability to interact programmatically with drives, folders, and files right from your Access application. You can retrieve information about available drives, check free space, handle folder navigation, and create temporary files. Meanwhile, handling the Not In List event in combo boxes and list boxes lets you build applications that are more responsive and helpful to users, without limiting them to preset values. By combining these advanced techniques, you can make your Access projects more powerful and user-oriented.