|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Welcome Welcome! Relink Files & Use File System Object Welcome to Microsoft Access Developer Level 33. In this course we will cover how to relink back-end files in your front-end Access database, whether using Access or SQL Server, including handling one or multiple back-end files. We will talk about using the Link Table Manager, automating relinking with VBA, and managing ODBC connections. We will also begin working with File System Object for file I/O, covering topics such as early versus late binding, creating folders, getting folder properties, looping through subfolders, and changing folder attributes. Some lessons build on previous Developer courses and basic file I/O concepts. NavigationKeywordsAccess Developer, relink backend, Link Table Manager, VBA relink tables, multiple backend files, ODBC relink, SQL Server, File System Object, file IO, early binding, late binding, CreateFolder, GetFolder, folder properties, subfolders, attributes
More InformationTranscriptWelcome 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 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 stuff before you get into the File System Object material, which is 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 Object material, and most of the code that I am going to be covering in today's class, has 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 all 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, called TechHelp. In fact, some of the videos in today's class were taken from some of my TechHelp videos as well. So there is lots of good stuff in there. Let us 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 has 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 point the back-end file, and then continue on. So 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 are going to learn about early binding versus late binding, the File System Object library, the Windows Script Host, and we are going to learn about the CurrentProject.Path command, which we have talked about before, but 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 we can 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, read only, and so on, and how to change those attributes. That is all in lesson six. IntroWelcome to Microsoft Access Developer Level 33. In this course we will cover how to relink back-end files in your front-end Access database, whether using Access or SQL Server, including handling one or multiple back-end files. We will talk about using the Link Table Manager, automating relinking with VBA, and managing ODBC connections. We will also begin working with File System Object for file I/O, covering topics such as early versus late binding, creating folders, getting folder properties, looping through subfolders, and changing folder attributes. Some lessons build on previous Developer courses and basic file I/O concepts. QuizQ1. What is the main purpose of using the Link Table Manager in Microsoft Access? A. To relink tables when back-end database files have moved or been renamed B. To convert tables to local tables C. To increase the performance of Access forms D. To encrypt the database Q2. Which lesson in this course covers relinking to a single back-end file using VBA? A. Lesson one B. Lesson two C. Lesson three D. Lesson six Q3. What does lesson three teach regarding back-end files? A. How to compact and repair backend files automatically B. How to relink multiple back-end files by looping through the attached tables C. How to create reports for multiple back-ends D. How to import data from Excel into multiple files Q4. What is essential to know before learning the File System Object material in this class? A. How to use the Link Table Manager B. Basic VB file I.O. concepts from previous Developer lessons C. How to create an ODBC connection to SQL Server D. How to design forms and reports Q5. Which command helps determine the folder where your Access database is located? A. GetDatabaseFolder B. DatabaseDir C. CurrentProject.Path D. GetCurrentFolder Q6. What does the File System Object's CreateFolder command do? A. Creates an Access form B. Deletes existing folders C. Creates a new folder in the file system D. Renames a table in the database Q7. What property would you use to find the creation date of a folder using the File System Object? A. FileSize B. DateCreated C. FolderPath D. AccessDate Q8. Which lesson discusses automatically relinking ODBC back-end files, including SQL Server? A. Lesson one B. Lesson four C. Lesson five D. Lesson six Q9. What can you access via the SubFolders collection when using the GetFolder command? A. All files in the folder B. All tables in the backend C. All subfolders under a specified folder D. All users that have access to the folder Q10. Which attribute is NOT mentioned as one you can change with the File System Object? A. Hidden B. System C. Read only D. Executable Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-C; 7-B; 8-B; 9-C; 10-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 focuses on advanced techniques for managing your Microsoft Access databases, specifically targeting issues with relinking back-end tables in your front-end database. I'm Richard Rost, and in today's class I'll show you how to handle relinking whether your data is on another Access file, an online SQL Server, or even spread across multiple back-end files. We'll also continue our exploration of file input/output, diving further into the File System Object, which is an essential topic for any Access developer working with files and folders. Since this is Developer Level 33, make sure you've completed all of my prior Beginner, Expert, and Developer-level courses, especially Developer 30 and 31, where we cover the basics of VB file input/output. Those earlier lessons will prepare you for today's more advanced material. If you're thinking of skipping lessons, I strongly advise against it. In fact, I feel so strongly about following the recommended order that I've written an article on the subject, so check that out as well. For reference, I'll be using Access 365, but if you're on Access 2019, you'll find nearly all of this content relevant. The File System Object code that I'll show you has worked all the way back to Access 2000. If any questions come up, feel free to scroll down and post them below. Either I or some of my advanced developer students in the forum will get back to you. Don't forget to take advantage of the TechHelp series too; I frequently share unique tips there, and some of the material in this course comes right out of those TechHelp episodes. Let's go over what we'll be learning today. In lesson one, we'll see how to use the Link Table Manager to reconnect tables when back-end database files have been moved or renamed. Lesson two introduces VBA methods to automate relinking to your back-end file. This way, if the file has moved, you'll be prompted to locate it, and Access will handle the relinking automatically - perfect for scenarios where you have a single back-end database. Lesson three steps it up by teaching you how to deal with multiple back-end files. We'll walk through looping over all tables in your database, determining which are attached, checking their connection status, prompting the user for any missing files, and using VBA to reattach them. This is especially useful if your application links to several different databases. In lesson four, we'll look at relinking ODBC connections, such as those used for SQL Server back-ends, allowing Access to reconnect automatically if anything changes. Then, in lesson five, we're back to file input/output. We'll introduce the File System Object, discuss early versus late binding, setting references to the File System Object library, and using commands like CurrentProject.Path to find the folder where your Access database resides. We'll move on to creating folders, including how to use code to generate single or multiple subfolders where you need them. In lesson six, we build on that by using the GetFolder command in the File System Object to grab a reference to any folder you need. Once you have this reference, you can examine the folder's properties, look through its SubFolders collection, and generally get an inventory of everything inside. We'll cover inspecting and working with properties like date created, date last accessed, file sizes, and other vital data management attributes. Additionally, we'll explore attributes such as hidden, system, and read-only, as well as how to programmatically modify those attributes as needed. 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 Link Table Manager to relink tables Automatically relinking to a back-end file with VBA Prompting users to browse for a moved back-end file Relinking multiple back-end files using a loop Identifying and handling attached tables in VBA Automatically relinking ODBC/SQL Server back-end files Introduction to the File System Object (FSO) Early binding vs late binding the FSO library Using the Windows Script Host for FSO Getting the database folder path with CurrentProject.Path Creating folders using the CreateFolder command Creating multiple subfolders in a loop Using GetFolder to reference a folder object Accessing SubFolders collection and listing subfolders Retrieving file and folder properties with FSO Getting folder size in bytes Working with file and folder attributes (hidden, system, read-only) Changing file and folder attributes with FSO Primary Topics
Access table relinking, Link Table Manager, VBA automated relinking, ODBC/SQL Server relinking, File System Object overview, file and folder properties, early vs late binding Secondary Topics
CurrentProject.Path usage, folder creation, subfolder iteration, file/folder attributes |
||
|
| |||
| Keywords: Access Developer, relink backend, Link Table Manager, VBA relink tables, multiple backend files, ODBC relink, SQL Server, File System Object, file IO, early binding, late binding, CreateFolder, GetFolder, folder properties, subfolders, attributes PermaLink How To Relink Back-End Files and Use the File System Object in Microsoft Access |
