Welcome

Today's class continues our work with Classic VB File Handling. We will start out learning how to navigate between files and folders, select files with the file picker, launch files with ShellEx, copy files, create folders, and more. Then we'll learn how to automatically compact multiple back-end files. Lots to do in this class!

Resources

Topics Covered

In Lesson 1, we start off with VB file and folder navigation, creating your own file browser, launching applications with the ShellExecute function, and using the standard Windows file picker.

In Lesson 2, we're going to learn how to copy files. We'll learn the FileCopy command. We'll create a central Images folder on your database server. We'll learn how to check to see if a file and folder exists. We'll create a folder with the MkDir command. We'll read the user's Windows system environment variables so we can determine where their User folder is and set that as the default file browse location. We'll store the last folder browsed in a TempVars variable. We'll learn how to separate filename and folder from the full path. We'll see how to get the length of a file with FileLen to check and make sure that the file copied 100% completely.

In Lesson 3, we will delete the old profile picture if a new one is selected with the Kill command. We'll ask the user first if it's OK before continuing, of course. We're not animals. We will verify the file has been deleted. We will timestamp new files as they are copied so that we don't run into duplicate filenames. We'll discuss error handling ramifications and best practices with File I/O functions. We'll see how to rename a file. I'll also talk about ChDrive and ChDir, although I almost never use them.

In Lesson 4, we will design a form to auto-compact multiple backend ACCDB files. If you have a split database and have multiple backend linked files, you'll be able to compact them all on a regular basis (like once a week) with a single click of a button. The database will allow you to pick your BE folder, loop thru the ACCDB files in that folder with a DIR, backup each file with a timestamp, compact it, and verify everything was done correctly. This one is a real time saver if, like me, you've got a dozen or so BE files.

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Questions?

Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.

Comments for Access Developer 31

Age	Subject	From
2 years	Dev 31 lesson 4	John Davy
4 years	Commas in Folder and File Names	JoAnn Brookover

Subscribe to Access Developer 31
Get notifications when this page is updated

Intro

In this lesson you will learn advanced Visual Basic techniques for file and folder navigation, copying files, and compacting multiple backend databases in Microsoft Access. I will show you how to use file pickers, create custom folder navigation forms, open files with shell execute, and check file attributes. You will also learn how to copy files, create folders when needed, work with file and folder paths, check file existence, and handle timestamps and error checking. Finally, I will show you how to build a utility to compact multiple backend Access files with automatic backup and error handling. This is lesson 31.

Transcript

Welcome to Microsoft Access Developer Level 31 brought to you by accesslearningzone.com. I am your instructor, Richard Rost.

Today's class is going to focus on VB file and folder navigation, copying files, and compacting multiple Access backend files.

This class, of course, follows Access Developer Level 30, and I started covering VB file I.O. in Developer 30, so I strongly recommend you take Developer 30. Also, the entire series before that - there is a bunch of beginner, expert, advanced, and developer lessons, and I strongly recommend you take all of those before diving into this one.

It is currently August of 2021, so I am using Access 365, which is roughly equivalent to Access 2019.

If you have any questions about the material covered in today's class, just scroll down to the bottom of the page that you are on and post them right there on the bottom of the page. If you have any other questions about Access that might not be related to this class, post them in the Access Forum.

Let's take a quick look at exactly what is covered in today's class.

In lesson one, we are going to learn how to navigate files and folders right from inside of Access. First, I am going to show you the file picker, which is pretty handy if you just want to pick a file and do something with it. It is a real simple couple lines of code. But sometimes you want to actually navigate through the files and folders yourself with your own Access form, so we are going to show you how to set that up - the folder check by our folder list box, the file list box. We will use the DUR function, the get attribute function to determine what are directories and what are folders and files.

We will learn how to move up and down and navigate through the folders, parent folders, and subfolders. We will learn how to open up files of any type using the shell execute function. Then we will learn how to determine the length of a file using the file length function. Lots of stuff in this lesson.

In lesson two, we are going to learn how to copy files from one location to another. I have covered bits and pieces from this lesson in various other videos. However, there is a lot of new stuff in this one too. We are going to learn about the file copy command to copy files. We are going to create a central images folder so you can pick a file and then it will copy it to your server images folder.

We are going to learn how to check if a file and a folder exist with the file exists and folder exists functions. If the folder you want to copy to doesn't exist, we will learn how to create that folder. We will see how to read the Windows system environment variables so we can tell what the user's profile folder is where he stores his pictures.

We will learn how to separate a file from the folder, so if you have a complete path, we can pull the file out, we can pull the folder name out. We will learn how to check the length of a file in bytes so we can make sure that the file copied correctly. Lots to cover in this video.

In lesson three, we are continuing with copying files. We are going to delete the old profile picture if it exists. We will prompt the user first and make sure it is okay. Then I will show you the kill command to delete a file. We will verify the file was deleted. Then I will show you how to timestamp files so that when you copy a file, in case you are trying to copy it over an old one with the same file name, this will give it a new file name to make sure that all files are unique. We will talk about some file IO error handling concerns and I will show you how to rename a file.

In lesson four, we are going to make a form to compact multiple back end files. If you have a split database, you can use this routine to have Access loop through all of the files in your back end folder that end in ACCDB. Use the DB engine compact repair tool on them, compact each one, verify everything copied, and automatically back up files before compacting them just to be safe. We will verify everything and we will put error handling on it.

This is something that you will run maybe once a week. You will load this little utility up, click the button, and it will compact all your back end databases and then you are good for the week.

Quiz

Q1. What is the primary focus of Microsoft Access Developer Level 31?
A. Designing user interfaces in Access
B. Advanced SQL queries and reporting
C. VB file and folder navigation, copying files, and compacting multiple Access backend files
D. Building SharePoint integrations with Access

Q2. Which previous level is particularly recommended before taking Level 31?
A. Access Developer Level 10
B. Access Developer Level 20
C. Access Developer Level 30
D. Access Developer Level 5

Q3. What tool is introduced for selecting files directly within Access?
A. Access Query Wizard
B. File picker
C. Form Designer
D. Table Analyzer

Q4. What function is used to distinguish between folders and files in navigation forms?
A. Lookup function
B. Search function
C. DUR function and get attribute function
D. Sum function

Q5. Which command is highlighted for copying files in lesson two?
A. TransferText command
B. Export command
C. FileCopy command
D. SaveAs command

Q6. What is a key step when the destination folder for a file copy does not exist?
A. Skip the copy process
B. Alert the user only
C. Use a function to create the folder
D. Move the file to a default folder

Q7. Which task is covered to help users find their Windows profile folder?
A. Running a directory listing
B. Reading Window system environment variables
C. Searching in Windows Explorer
D. Asking the user to type the path

Q8. To ensure the uniqueness of copied files, what method is taught?
A. Use random file names
B. Add file size to the file name
C. Timestamp copied files
D. Copy files without renaming

Q9. What command is demonstrated for deleting files?
A. Remove command
B. Erase command
C. Delete command
D. Kill command

Q10. What is the purpose of compacting Access backend files as shown in lesson four?
A. To merge multiple databases into one
B. To reduce database size and repair files
C. To convert files to a new format
D. To export data to Excel automatically

Q11. What safety measure is recommended before compacting backend databases?
A. Run a virus scan
B. Backup files automatically
C. Reset user passwords
D. Disconnect all users from the database

Q12. How often does the instructor suggest running the compacting utility?
A. Every day
B. Once a month
C. Once a week
D. Only after a crash

Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-C; 9-D; 10-B; 11-B; 12-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 focuses on some advanced techniques for working with files and folders in Microsoft Access using VBA. In this class, I will guide you through working with VB file and folder navigation, file copying operations, and tools to compact multiple backend Access database files.

This class builds directly on what I covered in Access Developer Level 30, where I first introduced VB file input and output. If you have not completed Developer Level 30, as well as the beginner, expert, advanced, and previous developer courses in this series, I strongly recommend you complete those first for the best learning experience.

For reference, I am working with Access 365, which is essentially the same as Access 2019, and recording in August 2021.

If you have questions regarding the material from this class, you can post them at the bottom of the course page. For other Access-related questions not specifically related to this video, please use the Access Forum.

Here is an overview of what I will be covering in today's class:

In the first lesson, I will explain how to navigate files and folders directly within Access. This starts with using the file picker dialog, which allows you to select and work with files using just a few simple lines of code. However, there are instances when you need more control and want to create your own navigation system inside Access. I will show you how to do just that by setting up folder and file list boxes on your forms. We will use the DIR function and an attribute checking function to determine which entries are folders and which are files. I will also go over navigation techniques: moving up and down through the folder structure, accessing parent folders, and browsing subfolders. You will also learn how to open files of any type using the ShellExecute function, and how to check the size of a file with a custom function that returns its length in bytes.

In lesson two, the focus shifts to copying files between locations. While I have covered some of these skills in other videos, there are many new and advanced methods included here. I will cover the FileCopy command, which is the main method for copying files. You will learn how to create a central images folder and how to select a file, then copy it over to this dedicated folder on your server. I will show you how to check if a file exists or if a folder exists with custom functions designed for that purpose, and how to create the folder if it is missing. You will see techniques for working with Windows system environment variables to determine locations such as the user's profile or pictures folders. I will also provide ways to separate out a file name from a file path and vice versa, and how to check file sizes to verify successful copying.

Lesson three continues with file management tasks. Here I will demonstrate how to delete an old profile picture if it exists, making sure to prompt the user for confirmation before doing so. I will introduce the Kill command for removing files and show you how to ensure the deletion was successful. Another topic in this lesson is creating timestamped file names when copying, so you avoid overwriting existing files with the same name and ensure every file is unique. We will touch on error handling for file input/output and review how to rename files as well.

Lesson four is about creating a utility to compact multiple backend Access database files. This is especially useful for those running split database systems. I will show you how to automate Access so that it cycles through all the backend files in a folder with the ACCDB extension and uses the DBEngine CompactRepair method to compact and repair each one. The system will also backup each database before compacting to maintain safety, verify the success of each operation, and apply error handling throughout the process. This tool is designed for regular maintenance – you can run it once a week to keep all your backend databases streamlined and healthy.

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

File picker implementation in Access
Custom folder and file navigation forms
Using folder and file list boxes
Identifying directories and files with DIR and GetAttr
Navigating parent and subfolders in Access
Opening files with the ShellExecute function
Determining file length using VBA functions
Copying files with the FileCopy command
Creating a central images folder for uploads
Checking if files and folders exist with custom functions
Creating folders programmatically in Access
Reading Windows system environment variables
Extracting file names and folder paths from full paths
Verifying file copy through file length comparison
Deleting files with the Kill command
Prompting users before deleting profile pictures
Verifying successful file deletion
Timestamping files for unique file names
Renaming files in VBA
Compacting multiple Access backend files
Looping through backend ACCDB files in a folder
Using the DBEngine.CompactDatabase method
Automatically backing up files before compacting
Implementing error handling for file operations