Welcome

Today's class begins with automatically relinking back-end files (with VBA, of course). We'll start out reviewing the Linked Table Manager, and then we'll learn how to determine whether or not our back-end file needs to be relinked. If so, we will relink a single back-end file using VBA. Then we'll learn how to relink multiple back-end files. I'll also cover how to relink to ODBC sources including SQL Server (even online databases).

Then, we'll learn about the File System Object to work with files and folders. We started learning about File I/O in Developer 30. Today we'll continue with a more powerful method. We'll learn about Early v. Late Binding of objects, how to create a folder, loop to create multiple folders, determine the size of a folder (and subfolders/files) and how to read and change the properties and attributes of folders and files.

Resources

Topics Covered

In Lesson 1, we will learn how to use the Linked Table Manager to relink your tables just in case your back-end database file has been moved or renamed. This and lesson 2 are free bonus lessons which don't count towards the hour for this class. Yes, we have covered this material before in earlier lessons, but it's a good review before lessons 2 and 3 which get into more advanced material.

In Lesson 2, we will learn how to use VBA to automatically relink to the back-end file. The database will recognize that the tables are missing, prompt you for the new back-end file location, and then relink it for you. We will use the TableDefs collection and my PickFile function. This solution will work for a single back-end file.

In Lesson 3, we will learn how to automatically relink to multiple back-end files. We'll loop through all of the tables in the database and see if each table is linked. If not, we will prompt the user for the location, relink it, and continue on to the next table.

In Lesson 4, we will learn how to automatically relink ODBC tables, including those linked to SQL Server (both local and online databases).

In Lesson 5, we will learn how to work with the File System Object. We'll talk about Early vs. Late Binding, the Windows Script Host, how to find the database's folder with CurrentProject.Path, how to create a folder, and how to loop to create multiple subfolders.

In Lesson 6, we are continuing with the File System Object. We'll learn how to use GetFolder to create a folder object that we can work with to get it's properties and collections. We'll learn about the SubFolders collection, file and folder properties, how to determine the size of a folder (and all of its subfolders and files), read the attributes of a file/folder, and how to change those attributes.

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 33

Age	Subject	From
2 years	Another Great Lesson	Sandra Truax
3 years	Const in Default Value	Eduardo Benaim

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

Intro

In this lesson, you will learn how to relink back-end database files in Microsoft Access using the Link Table Manager and VBA, covering both single and multiple back-end scenarios as well as ODBC connections like SQL Server. We will also get into using the File System Object for advanced file operations, including creating folders, looping through subfolders, and working with properties and attributes such as DateCreated and ReadOnly. This is Microsoft Access Developer Level 33.

Transcript

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

Today's class will teach you how to relink your back-end files into a front-end, whether it is with Microsoft Access, SQL Server Online, one or multiple back-end files. You name it.

Then we will start working with the File System Object, getting back into our File I.O. lessons. This will be Parts 1 and 2 of that.

Of course, this is the 33rd level of my Developer courses. I recommend you have taken all my Beginner, Expert, and other Developer courses. I am also going to add Developer 30 and 31 for the other File I.O. lessons where we talk about basic VB file I.O. It is good to know that material before you get into the File System Objects, which are a little more advanced.

I recommend you do not skip levels. In fact, I recommend it so much, I wrote a whole article about it, so go read that page.

I will be using an Access 365 subscription. It is currently October of 2021, so it is roughly equivalent to Microsoft Access 2019. The File System Objects and most of the code I am going to be covering in today's class have worked all the way back to Access 2000, I am pretty sure.

If you have questions, just scroll down the page and post them at the bottom. We will be sure to answer them as soon as we can. Or you can post your general Access questions in the forum. It is not only me, but also my other advanced developer students. I love them very much, and they help me out a lot by answering questions as well.

Be sure to check out my kind-of-almost-sort-of daily video podcast about Access that covers all the weird stuff: TechHelp. In fact, some of the videos in today's class are taken from some of my TechHelp videos as well, so there is lots of good stuff in there.

Let's take a closer look at what we are going to be covering in today's class.

In lesson one, we are going to learn how to use the Link Table Manager to relink your tables, just in case your back-end database files have been moved or renamed.

In lesson two, we will learn how to use VBA to automatically relink to the back-end file. It will prompt you and say, "Hey, looks like your file is moved, where is it?" Browse to the file, select it, and then Access will relink to it automatically. This method will be good for relinking to a single back-end file.

In lesson three, we will cover multiple files. In lesson three, we are going to loop through all of the tables in the database, find out if they are attached tables or not, check to make sure they are attached, and if not, prompt the user where to put the back-end file, and then continue on. You can have multiple back-end files and reattach them using the methods in lesson three.

In lesson four, we will learn how to automatically relink ODBC back-end files, including those to SQL Server.

In lesson five, we are returning to File I.O. We are going to start working with the File System Object. We will learn about early binding versus late binding, the File System Object library, the Windows Script Host, and the CurrentProject.Path command, which we have talked about before. That is how you figure out what folder your Access database is located in.

Then we will learn how to create a folder with the CreateFolder command, and we will learn how to loop to create multiple subfolders.

In lesson six, we are continuing with the File System Object. We will learn about the GetFolder command, where we can get an object reference to a folder, then access all of its properties and collections, such as its Subfolders collection, and get a list of all the subfolders under a folder.

We will learn about file and folder properties, things like DateCreated, DateLastAccessed, and all that stuff. The size of the folder is very handy, where you can get a byte size of all the files and folders under a folder. That is really huge.

Then we will learn about the attributes, like Hidden, System, ReadOnly, and so on, and how to change those attributes. That is all in lesson six.

Quiz

Q1. What is the primary focus of Microsoft Access Developer Level 33?
A. Creating reports in Access
B. Learning how to relink back-end files and using the File System Object
C. Designing user forms
D. Importing Excel data into Access

Q2. Why does the instructor recommend not skipping course levels?
A. Advanced material is only available at higher levels
B. Each level builds on concepts from previous courses
C. Beginner levels are not important
D. Older courses are outdated

Q3. Which tool is taught for relinking tables if back-end database files are moved or renamed?
A. Query Designer
B. Link Table Manager
C. Macro Builder
D. Relationships window

Q4. What happens in lesson two according to the course outline?
A. Importing data from Excel
B. Automating relinking to a single back-end file using VBA
C. Creating relationships between tables
D. Securing a database with passwords

Q5. What is the topic of lesson three?
A. Using Access forms for data entry
B. Reattaching multiple back-end files programmatically
C. Exporting data to text files
D. Designing custom queries

Q6. Lesson four covers which specific type of back-end file linkage?
A. XML file linkage
B. ODBC back-end files, including SQL Server
C. Flat file linkage
D. CSV file linkage

Q7. Which programming object is introduced when returning to File I.O.?
A. Recordset Object
B. File System Object
C. Database Object Library
D. API Object

Q8. What is the difference discussed regarding early binding vs. late binding?
A. Early binding is only available in SQL Server
B. Late binding automatically creates tables
C. Early and late binding are methods for referencing libraries in VBA
D. Late binding is always faster than early binding

Q9. What command is used to determine the location of your Access database file?
A. GetDatabasePath
B. CurrentProject.Path
C. LocationFile
D. FindPath

Q10. Which operation is demonstrated using the CreateFolder command?
A. Uploading a file to the internet
B. Creating a new folder on the system
C. Deleting a folder from the system
D. Printing a folder list

Q11. What collection is used to get a list of all subfolders in a folder with the File System Object?
A. Subfolders collection
B. SubFiles collection
C. AllFolders collection
D. FolderFiles collection

Q12. Which file or folder properties are mentioned as being accessible via the File System Object?
A. Only the folder name
B. Only the size and hidden attributes
C. DateCreated, DateLastAccessed, and folder size
D. Only file extensions

Q13. What types of attributes can be changed using the File System Object?
A. Network protocol attributes
B. Hidden, System, and ReadOnly
C. Database primary keys
D. Form object properties

Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B; 11-A; 12-C; 13-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 focuses on advanced techniques for relinking back-end database files in Microsoft Access, as well as introducing more sophisticated file input/output operations using the File System Object.

In this Developer Level 33 class, I will show you how to manage the process of relinking your front-end database to one or more back-end data sources. Whether you are working with Access back-end files, SQL Server Online, or handling multiple back-end files, these lessons will give you tools to handle different scenarios.

We will also get back into working with files on your computer, building on earlier File I.O. material. This time, we will be spending considerable time with the File System Object, and today's video covers Parts 1 and 2 of that topic.

If you are joining me here, you should have already completed all previous Beginner, Expert, and Developer courses. In particular, Developer Levels 30 and 31 are important prerequisites for the file operations we are going to discuss, because they cover the basics of VB file I.O. knowledge you will need moving forward. If you have not seen those lessons yet, please review them so you are up to speed.

I strongly advise against skipping any levels. This is so important that I wrote a separate article on why following the course sequence matters, so I recommend reading that as well.

For reference, I am using Microsoft Access 365 as of October 2021. This version is similar to Access 2019, but rest assured, almost all the material covered here regarding the File System Object and VBA code has worked consistently as far back as Access 2000.

If anything is unclear or you get stuck, you are encouraged to post your questions at the bottom of the course page. I check in as often as I can, but there is also a dedicated forum where both I and other experienced developers in the community help answer your Access questions.

For those of you interested in more informal lessons and solutions to unusual problems, take a look at my nearly daily TechHelp video podcast. Some topics discussed in today's class were first introduced there, so you may find it a valuable resource.

Here is an overview of what we will cover in this class:

Lesson one shows how to use the Link Table Manager to manually reconnect your tables if you have relocated or renamed your back-end database files.

Lesson two introduces a VBA solution for automatically relinking the back-end file. If Access cannot find the back-end file, a prompt will appear so you can select the correct file, and Access will re-establish the link for you. This is especially useful if your project only has a single back-end.

Lesson three expands on this by tackling setups with multiple back-end files. We will write a loop to go through all the tables in your database, determine which ones are linked, verify their connections, and prompt the user to locate any missing back-end files. This makes it easier to work with multiple back-end sources.

Lesson four focuses on relinking tables that use ODBC connections, such as those pointing to a SQL Server back-end. I will show you how to handle those scenarios so that everything reconnects smoothly.

Starting in lesson five, we switch over to File I.O. using the File System Object. You will learn the difference between early and late binding, how to reference the File System Object library, and the role of the Windows Script Host. We will revisit the CurrentProject.Path command, which helps you determine the folder where your Access database resides. This knowledge is crucial for many file operations.

You will also learn how to create new folders using the CreateFolder command and how to script the creation of multiple subfolders automatically.

Lesson six continues with the File System Object, introducing the GetFolder command. This allows you to work directly with folders, accessing detailed information and collections such as Subfolders. You will see how to create a list of all subfolders under any given folder.

We will go through important file and folder properties, including DateCreated, DateLastAccessed, and folder size. Being able to quickly determine the total size of a folder and its contents can be incredibly useful for data management.

Finally, we will discuss file and folder attributes like Hidden, System, and ReadOnly, as well as how to change these attributes with the File System Object.

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

Using the Link Table Manager to relink tables
Relinking tables with VBA for a single back-end file
Looping through tables to relink multiple back-end files
Automatically relinking ODBC back-end files including SQL Server
Introduction to the File System Object in VBA
Early binding vs late binding with File System Object
Referencing the File System Object library
Using Windows Script Host for file operations
Using CurrentProject.Path to get database folder location
Creating folders with the CreateFolder command
Looping to create multiple subfolders
Getting folder objects with GetFolder command
Accessing folder properties and collections
Listing subfolders with the Subfolders collection
Retrieving file and folder properties (DateCreated, DateLastAccessed)
Calculating folder size and contents in bytes
Getting and modifying file and folder attributes (Hidden, System, ReadOnly)