|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Welcome Welcome! Folders, File Copy, and Compact DBs Welcome to Microsoft Access Developer Level 31. In this course we will focus on VB file and folder navigation within Access, including using a file picker and working with Access forms for folder browsing. We will discuss using functions like DIR and GetAttribute to differentiate files and folders, how to use ShellExecute to open files, and determine file sizes with FileLen. We will walk through copying files with FileCopy, creating folders, checking file and folder existence, handling Windows environment variables, and renaming or deleting files. Finally, we will cover compacting multiple backend files in a split database using DBEngine CompactRepair. NavigationKeywordsAccess Developer, VB file navigation, folder navigation, file picker, Access form file browser, DIR function, GetAttribute, ShellExecute, FileLen, FileCopy command, check file exists, check folder exists, create folder, environment variables, timestamp fi
More InformationTranscriptWelcome 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. I started covering VB file I.O. in Developer 30, so I strongly recommend you take Developer 30, and also 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 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. Sometimes, however, you want to actually navigate through the files and folders yourself with your own Access form. 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, the GetAttribute 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 ShellExecute function. Then we will learn how to determine the length of a file using the FileLen 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 FileCopy 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 FileExists and FolderExists 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 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 I.O. 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 DBEngine CompactRepair tool on them, compact each one, verify everything copied, 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, it will compact all your back end databases, and then you are good for the week. IntroWelcome to Microsoft Access Developer Level 31. In this course we will focus on VB file and folder navigation within Access, including using a file picker and working with Access forms for folder browsing. We will discuss using functions like DIR and GetAttribute to differentiate files and folders, how to use ShellExecute to open files, and determine file sizes with FileLen. We will walk through copying files with FileCopy, creating folders, checking file and folder existence, handling Windows environment variables, and renaming or deleting files. Finally, we will cover compacting multiple backend files in a split database using DBEngine CompactRepair. QuizQ1. What is the main focus of Access Developer Level 31? A. File and folder navigation, copying files, and compacting backend Access databases using VB B. Creating custom Access reports and queries C. Designing user interfaces for Access applications D. Advanced database security techniques Q2. What function can you use to have users select a file in Access? A. FilePicker B. Dir C. OpenFileDialog D. FileLen Q3. When creating your own navigation through files and folders in Access, which function helps you distinguish between folders and files? A. FileCopy B. GetAttribute C. Len D. ShellExecute Q4. Which function allows you to open files of any type from Access? A. FileLen B. Dir C. ShellExecute D. FileExists Q5. What is the purpose of the FileLen function covered in this class? A. Checking if a folder exists B. Determining the length of a file in bytes C. Renaming a file D. Deleting a file Q6. What does the FileCopy command do in Access VBA? A. Moves a folder to another location B. Copies one file from a source to a destination C. Deletes a file from the system D. Compresses a folder into a zip file Q7. If a folder you want to copy to does not exist, what should your VBA code do? A. Skip copying B. Ask the user for a new location C. Create the folder D. Copy to a random folder Q8. Which functions can you use to check if a file or folder exists before performing operations? A. FilePicker and Dir B. FileExists and FolderExists C. ShellExecute and FileLen D. CopyFile and DeleteFile Q9. What should you do before deleting an old profile picture when copying a new one? A. Rename it B. Prompt the user for confirmation C. Move it to another folder D. Do nothing and delete it directly Q10. What command can be used in VBA to delete a file? A. RemoveFile B. FileDelete C. Kill D. CleanUp Q11. What technique can you use to prevent overwriting files with the same name when copying? A. Compress the new file B. Timestamp the new file name C. Skip the copy process D. Prompt the user to close the file Q12. What tool in VBA is used to compact and repair backend Access database files? A. DBCompactTool B. DBEngine CompactRepair C. FileLen Compact D. ShellCompact Q13. Before compacting backend databases, what is a recommended safety practice mentioned in the class? A. Skip files larger than 1 GB B. Manually close all files C. Automatically back up files before compacting D. Rename all accdb files Q14. How often is it suggested to run the utility that compacts backend databases? A. Every day B. Once a month C. Only after a crash D. Once a week Answers: 1-A; 2-A; 3-B; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-C; 11-B; 12-B; 13-C; 14-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 is Microsoft Access Developer Level 31. I'm your instructor, Richard Rost. In this class, I'm going to focus on working with files and folders using VBA. We'll look at navigating through directories, copying files, and handling multiple Access backend files, particularly when it comes to compacting them for maintenance. This lesson builds directly on what we covered in Developer Level 30, where we started exploring file input and output in VBA. If you haven't watched that class, or any of the previous Beginner, Expert, Advanced, or Developer lessons in the series, I strongly recommend you do so first, since many of the concepts here rely on that foundation. It's August 2021 for this recording, so I'm working with Access 365, which is very similar to Access 2019. If you have any questions about today's class, I encourage you to post them at the bottom of the page for this lesson. For other Access-related questions, the Access Forum is the best place to ask. Here's an overview of what we'll be working on. In the first lesson, I'll show you how to navigate files and folders directly from Access. I'll introduce the file picker, which is a simple tool if you just need to choose a file and work with it. But sometimes you need more control, so I'll demonstrate how to create your own file and folder browsing form in Access. We'll use elements like a folder list box and a file list box. You'll learn about the DIR function and the GetAttribute function, which help you identify what's a directory versus a file. We'll also cover moving between folders, both parent and subfolders. I'll show you how to open all kinds of files using the ShellExecute function and how to check the file size using the FileLen function. Lesson two will focus on copying files from one place to another. I've touched on this topic in other videos, but we'll look at some new techniques as well. We'll use the FileCopy command to move files around, and set up a central images folder where users can pick a file to copy to a server location. Along the way, I'll show you how to check if files or folders exist with FileExists and FolderExists functions. If you try to copy a file to a folder that doesn't exist, you'll learn how to create that folder first. We'll also look at reading environment variables so you can locate folders like the user's profile or pictures directory. I'll explain how to split a full path into the filename and the folder portion. To ensure files copied correctly, I'll show you how to check their size in bytes. In lesson three, we'll continue working with file copying, but we'll also cover deleting files. For example, if you have old profile pictures to remove, I'll show you how to prompt the user for confirmation and use the Kill command to delete files, then check that the file is truly gone. I'll explain how to add a timestamp to filenames when copying, which helps prevent conflicts with duplicate filenames. We'll also discuss error handling as it relates to file operations, and I'll go over file renaming techniques. The final lesson is about compacting multiple backend database files. If you've split your database into a frontend and several backend files, you'll learn how to have Access cycle through all your backend files, compacting each one with the DBEngine CompactRepair tool. We'll make sure every file is compacted and copied correctly, including backing up files before making changes, just to be sure nothing is lost. This utility is something you might run weekly to keep your backend databases running smoothly. You just open the utility, click a button, and it takes care of compacting all the backend databases for you. 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 ListFile picker dialog in Access Navigating files and folders in Access forms Using list boxes to display folders and files DIR function to list directories and files GetAttribute function to distinguish folders from files Navigating parent and subfolders Opening files with ShellExecute Obtaining file size with FileLen Copying files using the FileCopy command Central images folder management Checking if files and folders exist Creating folders if they do not exist Reading Windows environment variables in VBA Extracting file and folder names from paths Verifying file copy by byte size Deleting files using the Kill command Prompting user before file deletion Timestamping files for unique filenames Renaming files in VBA Compacting multiple Access backend files Using DBEngine CompactRepair in VBA Backing up backend files before compacting Error handling for file operations Primary TopicsAccess VBA file navigation, file picker, DIR function, file/folder existence check, copying and renaming files, deleting files, compacting backend databases Secondary Topicserror handling, shell operations, environment variables, file attribute checks |
||
|
| |||
| Keywords: Access Developer, VB file navigation, folder navigation, file picker, Access form file browser, DIR function, GetAttribute, ShellExecute, FileLen, FileCopy command, check file exists, check folder exists, create folder, environment variables, timestamp fi PermaLink How To Navigate Folders Copy Files and Compact Backend Databases in Microsoft Access |
