Welcome

Today's class continues working with the File System Object. We'll do a lot with copying files and folders (including entire folders along with their subfolders). We will learn how to read and write files. We'll make a customer data change file that we can email to the customer, have them make changes, and then import those changes back into the database. We'll see how to send email with Outlook.

Resources

Topics Covered

In Lesson 1, we will continue working with the File System Object. We will learn about CopyFolder, MoveFolder, DeleteFolder, listing all files in a folder, CopyFile, CopyFile with wildcards, CopyFile with a loop of the Files collection, and how to copy files based on their properties (files of a certain size, modified date, etc.).

In Lesson 2, we will learn how to write text files using the TextStream object. We will learn about CreateTextFile, Write, WriteLine, and WriteBlankLines. We will create a customer text file with all of the information we want to send to the customer for review and edit (name, address, phone, etc.) We will learn two ways to send email using Outlook (SendObject and Outlook.Application) so we can send the customer text file as an attachment. We'll convert the email code from Early to Late Binding.

In Lesson 3, we will learn how to read text files. We'll learn about OpenTextFile, Read, ReadLine, and ReadAll. We'll learn to tell when were AtEndOfLine and AtEndOfStream (the file). We will read in the changes that the customer made to their data file, update their customer record, and track changes in a change log.

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 34

Age	Subject	From
10 months	FSO	John Davy

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

Intro

In lesson 34, you will continue working with the File System Object in Microsoft Access to manage files and folders, including copying, moving, deleting, and listing files. You will learn how to use the TextStream Object to read and write text files, export customer data, and send files as email attachments using Outlook, both with early and late binding. The lesson also covers importing changes from files returned by customers and logging those updates in your database. This class builds directly on the skills taught in Developer 33.

Transcript

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

In today's class, we continue working with the File System Object that we started in Developer 33. We are going to learn how to read and write text files. This time we'll be using the File System Object, which is a little bit different than the classic VB file input/output we've done before.

We're going to learn how to use Outlook to send email. We've covered it briefly before, but I'm going to show you a new way of doing it today. This new method will allow us to send bulk mail and send attachments of any file type.

Then what we'll do is, knowing how to read and write text files, we're going to make it so we can export a customer's data file – first name, last name, and so on – and email it to them. Then, when they email it back to us, we'll be able to import those changes, update their customer record, and make a change log. All that's covered in today's class.

Of course, this is the 34th Developer Class. I strongly recommend you've taken all my Beginner, Expert, Advanced, and Developer classes before this, especially Developer 30 and 31, which cover basic file I/O, and Developer 33, which is part 1 and 2 of the File System Object, which is what we're doing in this class. So, definitely take Developer 33 before this one.

Otherwise, you won't understand a lot of stuff I'm talking about as far as File System Object and declaring it, and the early and late binding and all that stuff. So, I'm going to say Developer 33 is definitely a hard prerequisite for this one.

And of course, don't skip levels or read that page for more information as to why.

I am using Microsoft Access 365 as part of a 365 subscription, roughly equivalent to Access 2019, but everything covered in this class should go back to, I'm going to say, at least 2007.

Got questions? Post them down below in the bottom of this page or post them in my Access forum. And don't forget to check out my TechHelp series, my kind of almost daily sort of podcast about Access. I make about two or three new ones every week.

Let's take a quick look at what is covered in today's class.

In lesson one, we are continuing with the File System Object that we started in Access Developer 34. We're going to learn how to copy a folder, which copies all the subfolders and files in that folder. We'll learn how to move a folder and delete a folder. We'll learn how to list all of the files in a folder. Copy file, copy file with wildcards, copy file with a files collection loop, and then copy a file based on either its size or its date or any other property. That's all coming up in lesson one.

In lesson two, we're going to have some fun. We're going to learn about the TextStream Object where we can write text files. We'll talk about WriteLine, Write, write blank lines, and more. We'll create a customer text file with all the customer's details: customer ID, first name, last name, address, and so on. I'll show you how to loop through the controls on your form and export just the ones we want. Then I'll show you how to send that through Outlook email.

We'll cover both SendObject, which we've done before, and the Outlook application, so we can send it as a text file attachment to the customer. Then I'll show you how to convert some code from early to late binding, because I've got the email code set to early binding, where you need a reference to Microsoft Outlook. But we don't want that, so we're going to convert it in class to late binding. All that's in lesson two.

In lesson three, we're going to learn how to read text files back in. We'll learn about ReadLine, read by character, ReadAll, which is reading the whole file in one shot. We'll know if we're at the end of a line or at the end of the entire stream, which is the file. Then we'll read back in any changes the customer made from the file that we sent them in lesson two. We'll make those changes in our form, and we'll track the changes in a change log, which we'll just store in the notes field. All that's in lesson three.

Quiz

Q1. What is the primary topic covered in Access Developer Level 34?
A. Creating complex queries in Access
B. Working with the File System Object to manage files and folders
C. Building forms and subforms in Access
D. Designing advanced Access reports

Q2. Which object is used in this class to read and write text files?
A. Data Access Object (DAO)
B. ActiveX Data Objects (ADO)
C. File System Object (FSO)
D. TableDef Object

Q3. What essential prerequisite is strongly recommended before taking this class?
A. Beginner Level 1
B. Expert Level 15
C. Developer 33
D. Advanced Level 12

Q4. Which versions of Microsoft Access are compatible with the lessons presented in Developer Level 34?
A. Only Access 365
B. Access 2016 and later
C. Access 2019 and later
D. Access 2007 and later

Q5. What tasks related to folders are covered in lesson one?
A. Only listing files in a folder
B. Copying, moving, deleting folders, and listing files
C. Managing user permissions on folders
D. Linking folders to tables

Q6. In lesson two, what object is used for writing to text files?
A. Folder Object
B. TextStream Object
C. Recordset Object
D. Table Object

Q7. What methods will you learn to use on the TextStream Object in lesson two?
A. ReadOnly and Execute
B. WriteLine, Write, and write blank lines
C. Import and Export
D. Delete and Rename

Q8. Why is it important to convert code from early binding to late binding in the context of sending emails?
A. To avoid the need for an Outlook reference
B. To make the code run faster
C. To reduce file size
D. To change file formats

Q9. What process is covered regarding customer data in lesson two?
A. Sorting customer data by last name
B. Exporting customer data to a text file and emailing it
C. Importing customer data from an Excel file
D. Creating a customer report

Q10. What will you learn to do with text files in lesson three?
A. Convert them to images
B. Read them back in and track changes in a change log
C. Delete files permanently
D. Merge multiple files into one

Q11. What is one method mentioned for sending email with attachments in this class?
A. SMTP server only
B. Using SendObject and Outlook application methods
C. FTP transfer
D. Linked tables

Q12. How will changes made by a customer be handled after receiving the updated text file?
A. Ignored completely
B. Manually compared with the old data
C. Imported back and logged in a change log
D. Deleted after import

Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-B; 7-B; 8-A; 9-B; 10-B; 11-B; 12-C

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 continues our in-depth work with the File System Object in Microsoft Access, building on the foundation we set in Developer 33. In this lesson, I'm going to guide you through the process of reading from and writing to text files using the File System Object, focusing on how this approach differs from the traditional Visual Basic file input-output methods we have used in previous classes.

We'll also explore a new technique for sending emails through Outlook. Although we've touched on using Outlook before, this time I'll introduce a new method that gives you more flexibility. With it, you'll be able to send bulk emails and include attachments of any file type.

Once we have a solid handle on reading and writing text files, we'll put these skills into practice by exporting customer data to a text file. We'll select key details like first name, last name, and so on, and email this file to the customer. Later, when the customer sends their updates back to us, you'll learn how to import their changes, update the existing customer record, and create a change log to document what was updated.

This is the 34th part in the Developer series, and I highly recommend that you work through the Beginner, Expert, Advanced, and earlier Developer classes before tackling this one. In particular, make sure you've completed Developer 30 and 31, where we first discussed file input and output, and especially Developer 33, which covers the initial work with the File System Object. Understanding how to declare and use the File System Object, as well as the concepts of early and late binding, is essential before proceeding with today's content.

I'm using Microsoft Access 365 as part of a subscription, which is very similar to Access 2019. However, most of what we cover today should be applicable as far back as Access 2007.

If you have questions as you go through the material, you're always welcome to post them on this page or in my Access forum. And don't forget to check out my TechHelp video series, where I post several Access troubleshooting and tip videos every week.

Here's a breakdown of what you'll learn in this class:

In the first lesson, we continue exploring the File System Object from Developer 33. You'll learn how to copy entire folders, including all their subfolders and contents. We'll move and delete entire folders, list every file within a folder, and copy files individually or in groups using wildcards. We'll also go through copying files based on specific properties like size or date.

Lesson two shifts focus to the TextStream Object, which gives us the ability to work with text files in new ways. I'll show you how to write individual lines, entire blocks of text, and even blank lines to a file. We will assemble a customer text file containing all relevant details such as ID, name, and address, and I'll demonstrate how to loop through your form's controls to export only the necessary data. We'll then email this file to a customer, using both Access's built-in SendObject method and the Outlook Application method for more robust attachment support. You'll also learn how to adjust the email code from early binding, which requires a reference to the Outlook object library, to late binding so you don't need that reference.

In lesson three, we'll reverse the process and read the text files back into Access. I'll discuss the different ways to read the file, whether it's line by line, character by character, or all at once. You'll see how to detect when you've reached the end of a line or the end of the file. Once you've received the updated customer file back, I'll show you how to import the changes, update your forms, and log these edits in a notes field as a simple change log.

For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.

Topic List

Copying folders with the File System Object
Moving folders using the File System Object
Deleting folders with the File System Object
Listing all files in a folder
Copying files with and without wildcards
Copying files using a files collection loop
Copying files based on file size or date
Writing text files with the TextStream Object
Using Write and WriteLine for file output
Writing blank lines to text files
Creating a customer text file export
Looping through form controls for selective export
Sending text file attachments with Outlook
Using SendObject to email attachments
Sending emails using the Outlook application
Converting email automation from early to late binding
Reading text files with the TextStream Object
Using ReadLine to read text files
Reading files character by character
Using ReadAll to read entire text files at once
Detecting end of line and end of stream in files
Importing customer data changes from a text file
Updating form records from imported data
Recording a change log when importing updates