Introduction

Welcome! Folders, File Copy, and Compact DBs

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

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.

Navigation

Prev: D31
Next: Lesson 01

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

Subscribe to Introduction
Get notifications when this page is updated

Intro

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. 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.

Quiz

Q1. 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.

Summary

Today'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 List

File 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

Article

In this tutorial, I am going to teach you some practical ways you can work with files and folders using VBA in Microsoft Access. By the end of this lesson, you will know how to let users pick files from their computer, browse folders, copy files, check if files or folders exist, create new folders, and even compact several Access backend database files at once - all directly from your Access application.

Let us start by learning how to allow users to pick a file from their computer. Access has a handy file picker dialog you can use with just a few lines of VBA code. Here is an example of how to open the file picker dialog and retrieve the selected file path:

Dim fd As Object
Set fd = Application.FileDialog(3)
If fd.Show Then
MsgBox fd.SelectedItems(1)
End If

This code creates a file dialog box (type 3 is the file picker), displays it, and if the user picks a file, it shows the full path in a message box. You can use the path in your code for whatever you need to do next.

Sometimes you want more control and would like to let users browse through folders using an Access form instead of the default file dialog. For this, you can use the VBA Dir function to list the contents of a folder, then check whether each item is a file or a folder. Here is a basic example of how to loop through the files and folders in a given directory:

Dim strPath As String
Dim strFile As String

strPath = "C:\YourFolder\"
strFile = Dir(strPath, vbDirectory)

Do While strFile <> ""
If GetAttr(strPath & strFile) And vbDirectory Then
Debug.Print "Folder: " & strFile
Else
Debug.Print "File: " & strFile
End If
strFile = Dir
Loop

The Dir function returns the name of each file and folder in the directory, one at a time. The GetAttr function checks the attributes to see if the item is a directory. This is useful if you want to build your own file or folder navigation forms.

You can also let the user move up and down between folders in your folder browser by tracking the current path and adjusting it when the user selects a parent folder or a subfolder. Once you have selected a file, you might want to open it with the default program. For that, the ShellExecute function from the Windows API can be used, for example:

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Then, to open a file using its associated program:

Call ShellExecute(0, "open", "C:\YourFolder\YourFile.pdf", vbNullString, vbNullString, 1)

To find out the size of a file in bytes, you can use the FileLen function:

Dim lngSize As Long
lngSize = FileLen("C:\YourFolder\YourFile.txt")
MsgBox "File size is " & lngSize & " bytes"

Now let us move on to copying files. To copy a file from one location to another, Access has the simple FileCopy command. Here is how you use it:

FileCopy "C:\SourceFolder\Source.jpg", "C:\DestinationFolder\Destination.jpg"

If you want to copy an uploaded image, for example, to a central images folder, you might first check if the folder exists before copying. To check if a file exists, use:

Function FileExists(strFile As String) As Boolean
FileExists = (Dir(strFile) <> "")
End Function

To check if a folder exists, you can use:

Function FolderExists(strFolder As String) As Boolean
FolderExists = (Dir(strFolder, vbDirectory) <> "")
End Function

If the destination folder does not exist, you can create it using:

MkDir "C:\DestinationFolder"

Sometimes it is handy to get the user's profile folder or other Windows environment variables. You can use the Environ function for this:

Dim strProfile As String
strProfile = Environ("USERPROFILE")

This gives you the path to the user's profile, and you can use that to find their pictures, documents, etc.

To split a full file path into just the file name or just the folder, you can use basic string manipulation. For example:

Dim strFullPath As String
Dim strFile As String
Dim strFolder As String

strFullPath = "C:\Folder\Subfolder\File.txt"
strFile = Dir(strFullPath) ' Gets the file name
strFolder = Left(strFullPath, InStrRev(strFullPath, "\") - 1) ' Gets the folder path

Always make sure your copy operation worked by comparing the file length of the source and destination:

If FileLen("C:\SourceFolder\Source.jpg") = FileLen("C:\DestinationFolder\Destination.jpg") Then
MsgBox "File copied successfully"
Else
MsgBox "There was a problem copying the file"
End If

Next, you might want to handle deleting files. The Kill command lets you delete a file:

If FileExists("C:\DestinationFolder\OldImage.jpg") Then
If MsgBox("Delete old image?", vbYesNo) = vbYes Then
Kill "C:\DestinationFolder\OldImage.jpg"
If Not FileExists("C:\DestinationFolder\OldImage.jpg") Then
MsgBox "File deleted"
Else
MsgBox "File was not deleted"
End If
End If
End If

To ensure you do not overwrite an old file when copying a new one, you can timestamp the file name so each file is unique:

Dim strNewName As String
strNewName = "Image_" & Format(Now, "yyyymmdd_hhnnss") & ".jpg"
FileCopy "C:\SourceFolder\Source.jpg", "C:\DestinationFolder\" & strNewName

If you need to rename a file, use the Name command:

Name "C:\OldFolder\OldName.jpg" As "C:\OldFolderewName.jpg"

Whenever you are performing file operations, add some error handling to catch any issues. For example:

On Error GoTo ErrorHandler
FileCopy "C:\SourceFolder\Source.jpg", "C:\DestinationFolder\Destination.jpg"
Exit Sub

ErrorHandler:
MsgBox "Error copying file: " & Err.Description

Finally, let us look at how to compact multiple Access back end files. If you have a split database, keeping your back end files compacted keeps things running smoothly. Here is how you can loop through all the ACCDB files in a folder and compact each one using the DBEngine.CompactDatabase command:

Dim strPath As String
Dim strFile As String
strPath = "C:\BackendFolder\"
strFile = Dir(strPath & "*.accdb")

Do While strFile <> ""
Dim strSource As String
Dim strBackup As String
Dim strTemp As String

strSource = strPath & strFile
strBackup = strPath & "Backup_" & Format(Now, "yyyymmdd_hhnnss_") & strFile
strTemp = strPath & "Temp_" & strFile

' Back up the original file
FileCopy strSource, strBackup

' Compact into a temporary file
DBEngine.CompactDatabase strSource, strTemp

' Delete the original file and replace it with the compacted one
Kill strSource
Name strTemp As strSource

strFile = Dir
Loop

This routine backs up every database in the folder first, then compacts it in place. You might want to run this weekly or on a regular schedule.

With these examples, you should now feel comfortable adding file and folder navigation, copy, rename, deletion, checking, and compacting routines to your Access projects. These skills let you build more flexible and powerful database applications that interact smoothly with the Windows file system.