|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 31 Lessons Welcome to Access Developer 31. Total running time is 2 hours, 24 minutes. Lessons
Database FilesLinks
Resources
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn lesson 31 of my Microsoft Access Developer series, you will learn how to use VB code for file and folder navigation inside Access, including building custom navigation forms and using the file picker. I will show you how to copy files between folders, check for file and folder existence, read Windows environment variables, and ensure copied files are unique with timestamped names. You'll also see how to delete and rename files, handle common file I/O errors, and build a utility to compact multiple backend ACCDB files in a split database setup.TranscriptWelcome 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, take the entire series before that. There are 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 at 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 of 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 DIR function, and 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. There is 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 to see 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 does not 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 and 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, and 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. There is 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 backend files. If you have a split database, you can use this routine to have Access loop through all of the files in your backend folder that end in ACCDB. Use the DB engine compact repair tool on them to compact each one. Verify everything copied, automatically back up files before compacting them just to be safe. Of course, 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 backend databases and then you are good for the week. QuizQ1. What is the primary focus of Access Developer Level 31?A. User interface design in Access B. VB file and folder navigation, copying files, and compacting Access backend files C. Creating reports in Access D. Integrating Access with Excel Q2. Which function is used to determine if an item is a directory or a file? A. FileExists B. GetAttribute C. ShellExecute D. FileLength Q3. What function is demonstrated to open files of any type from Access? A. OpenFile B. UseShell C. ShellExecute D. LaunchApp Q4. How can you determine the length of a file in Access VBA? A. Use the FileCheck function B. Access the File property Length C. Use the FileLength function D. Check the FolderSize function Q5. What command is used to copy files from one location to another in VBA? A. MoveFile B. FileCopy C. CloneFile D. CopyFolder Q6. Which functions are taught to check for the existence of files and folders? A. PathExists and FolderVerify B. FileExists and FolderExists C. CheckFile and CheckFolder D. FindFile and FindFolder Q7. If the destination folder does not exist when copying a file, what does the lesson teach to do? A. Ignore the operation B. Show an error message C. Create the folder D. Copy the file to the desktop instead Q8. How does the course suggest ensuring unique filenames when copying over existing files? A. Overwrite existing files B. Do not copy if the file exists C. Add a timestamp to the filename D. Rename the source folder Q9. What is the purpose of the compacting routine taught in lesson four? A. To convert files to ACCDB B. To compress images C. To compact and repair multiple backend databases D. To reduce the number of front-end files Q10. What precaution is suggested before compacting backend database files? A. Ignore old files B. Back up the files automatically C. Only compact files smaller than 1MB D. Rename all files first Q11. What type of error handling is discussed in relation to file I/O operations? A. Basic error ignoring B. No error handling is needed C. Error handling to verify operations like copy, delete, and compact succeed D. Error handling only for renaming files Q12. How often is it suggested that you run the massive compact and repair utility? A. Every hour B. Only once a year C. Whenever you use the database D. About once a week Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-C; 9-C; 10-B; 11-C; 12-D 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. SummaryToday's video from Access Learning Zone covers Microsoft Access Developer Level 31. In this class, I will walk you through working with files and folders using VBA in Access, including tasks like navigating directories, copying files, and performing batch compaction on multiple Access backend files.This class builds directly on the material from Access Developer Level 30, where I first introduced VB file input and output. Because these lessons are sequential and cumulative, I recommend that you complete all previous levels, including the beginner, expert, advanced, and developer courses, before tackling this one. This class was recorded in August 2021 using Access 365, which is very similar to Access 2019. If any questions come up while you are working through this material, feel free to post them at the bottom of the class page. For general Access questions that are not specific to this class, the Access Forum is open for your posts. Here is a summary of what we will cover in this course. In the first lesson, you will see how to navigate files and folders from inside Access. I will demonstrate the file picker, which is a straightforward way to select a file if that is all you need. If you prefer to have more control, I will show you how to set up your own Access form for browsing directories and files. We will use list boxes for folders and files, make use of the DIR function, and employ the GetAttr function to figure out which entries are folders and which are files. You will learn how to move through folder structures, dealing with both parent and subfolders. I will also show you how to launch files of any type from Access using the ShellExecute function and how to check file sizes using a file length function. The second lesson focuses on copying files from one place to another. Although I have introduced some of these concepts in previous videos, there is plenty of new material here. We will go over the FileCopy command, and you will learn how to build a central folder for images so that users can select and copy files to a specific server directory. You will see how to check if a file or folder exists before copying, using custom functions for file and folder existence. If the target folder is missing, I will show you how to create it using VBA. In addition, we will explore how to read Windows environment variables to locate user profile folders and picture folders. There will also be a lesson on separating file names from full paths, checking file sizes to ensure successful copies, and other practical considerations. In the third lesson, we will continue with file management and cover deleting files. For example, if you need to remove an old profile picture, I will demonstrate how to prompt the user for confirmation before using the Kill command to delete a file. We will verify the deletion and move on to more advanced file naming. You will learn how to add timestamps when copying files so that even files with the same name are stored uniquely. We will address best practices for error handling during file operations and see how to rename files in VBA. The fourth and final lesson demonstrates how to create a utility in Access for compacting multiple backend files at once. If you are working with a split database, this tool will loop through all the ACCDB files in your backend folder and use the DBEngine CompactRepair feature to compact each one. It also includes routines to automatically back up your files before compacting as an added layer of safety. Each step is verified, and error handling is built in. This is the kind of maintenance task you might perform weekly. Simply launch the utility, run the process, and all your backend files will be compacted and backed up. 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 ListUsing the File Picker in Access VBACreating a custom folder navigation form Populating folder and file list boxes Using the DIR function to list files and folders Using GetAttr to distinguish files and folders Navigating parent and subfolders in VBA Opening files with ShellExecute Determining file size with FileLen Copying files with the FileCopy command Setting up a central images folder Checking if a file exists with VBA Checking if a folder exists with VBA Creating folders using VBA Reading Windows environment variables in VBA Extracting file names from full paths Extracting folder names from full paths Verifying file copy by checking file size Deleting files with the Kill command Prompting user before deleting files Verifying file deletion Adding timestamps to file names for uniqueness Handling file IO errors in VBA Renaming files using VBA Compacting multiple Access backend files Looping through backend files in a folder Using DBEngine.CompactDatabase Automatically backing up files before compacting Adding error handling to compact and backup routines |
||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access developer 31 lessons PermaLink How To Navigate Files, Copy and Rename Files, and Compact Backend Databases in Microsoft Access |
