Welcome

Today's class is all about Classic VB File Input & Output. We will learn how to read and write text files. There are a lot of reasons why you would want to learn how to manually work with file I/O routines with your Access database, and I'll spend some time going over them in Lesson 1.

Resources

Topics Covered

In Lesson 1, we will get an introduction to File Input/Output (File I/O). We're going to learn why you want to learn this stuff. We will learn about the two different types of file I/O: Classic VB and File System Object (FSO) which is newer. Then we'll learn the pros and cons of each.

Lesson 2 is a free bonus lesson. It's one of my TechHelp videos showing how to import data from Microsoft Excel CSV files and fixed width text files. It's a good foundation for what's coming next.

Lesson 3 is another free bonus lesson from my TechHelp videos. This one the members-only Extended Cut for the previous video where I show you how to import directly using VBA so it's a preview of what's going to be coming up in the rest of today's class. We're going to learn how to import a fixed-width file over 2 GB in size, which is larger than the Access maximum for a single database file or table.

In Lesson 4, we're going to start from the beginning with Classic VB File I/O. We're going to learn how to write out a text file. We'll learn the Open, Output, Print, Close, Append commands. We'll talk about what folders you can write to, what folders you have access to write to. CurrentProject.Path gets the folder that the database is in. FreeFile gives you an open file handle. How to use the Shell Command to open up Notepad to view your text file as soon as it's exported. We'll learn how to loop through the controls on a form so we can export all of the text box values from one form. Export customer data, customer list, and an invoice with all of the line item details and order total.

In Lesson 5, we will learn how to read text files. We will open a file for input, read in lines of text with the Line Input command. Read all lines to the EOF (End of File). We'll use a recordset to add values to a table from the file input. We will read in customer data and survey responses. I'll show you how to copy and paste a customer's survey response data into a field on a form, export that data to a file, and the read in and parse the data one line at a time.

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 30

Age	Subject	From
3 years	Import from Excel VB	Abraham Breuer

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

Intro

In this lesson, you will learn how to work with file input and output in Microsoft Access using classic VB methods. We will cover reading from and writing to text files, discuss the differences between classic VB file I/O and the newer File System Object, and look at scenarios where each is useful. Along the way, we will walk through exporting customer and invoice data to text files, using recordsets to import data back into tables, and handling tasks like appending data and opening files with Notepad. This is Microsoft Access Developer Level 30.

Transcript

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

Today's class is all about file input and output. We are going to learn how to work with text files. There are lots of reasons why, and I will go over them in a few minutes. Today we will be focusing on classic VB file input and output, reading and writing text files.

In Developer 31, we are going to get into the File System Object, which is a little more powerful and has some extra features. But I think it is important to learn classic VB file input and output too because I still use it since it is a lot easier for basic stuff.

This, of course, is Developer Level 30, so I recommend you have taken Developer 29 and everything before it. For example, in Developer 16, we learned recordsets, and in today's class we will be using recordsets when we read in text files and add that data to a table. We will use a recordset to do that. So do not jump around; take the classes in order, please, for your own benefit.

I will be using Microsoft Access as part of a Microsoft 365 subscription, roughly equivalent to Access 2019. However, everything that I cover in today's class goes way back. Even if you have got Access 2003, it should still work just fine. This stuff has not changed in years.

If you have any questions regarding the material covered in today's class, just scroll down to the bottom of the page that you are on right now. Post your questions there. Take a minute to read through any other questions that have been posted, as your question may have already been answered. Make sure you click on that big red subscribe button, and you will get notified if any other questions or comments are posted for this class.

Do not forget to visit the Access Forum if you have questions unrelated to today's class.

Now, let's take a closer look at exactly what is covered in today's lessons.

In lesson one, it is an introduction to file input and output, or file I.O. We are going to learn why you want to learn this stuff. We are going to learn the two different types of file I.O. There is classic VB and there is File System Object, which is newer. Then we will learn the pros and cons of each.

Lesson two is a free bonus lesson, one of my TechHelp videos showing how to import data from Microsoft Excel with CSV files and fixed-width data, and fixed-width text files. It is a good foundation for what is coming next.

Lesson three is another free bonus lesson from my TechHelp videos. This one is a members only extended cut for the previous video, where I show you how to import directly using VBA. So it is a preview of what is going to be coming up in the rest of today's class.

We are going to learn file I.O. to read in a text file that is larger than two gigabytes, which is larger than the Access maximum.

In lesson four, we are going to start from the beginning with classic VB file input and output. We are going to learn how to write out a text file - open, output, print, close, append commands. We will talk about what folders you can write to, what folders you have access to write to, and the current project.path to get the folder that the database is in.

We will learn about free file, how to use the shell command to open up Notepad to view your exported text file as soon as it is exported. We will learn how to loop through the controls on a form so you can export all of the text box data, for example, from one form.

We will export customer data - a customer list - and we will export an invoice with all of its line item details.

In lesson five, we are continuing with classic VB file I.O. - this is part two. We are going to learn how to read text files. We are going to learn how to open a file for input, read in lines of text using the line input command. We are going to learn how to read in multiple lines, reading to the end of file.

Then what we will do is take the customer data that we exported in the last lesson and learn how to read it back in, so we can send that file to someone else, like a sales rep. They can update the information, then you can read back in their responses right into their customer record in the database.

Then I will show you how to send out and read survey responses. You can send out a survey to your customer, they email it back to you, copy and paste it into your database, and then export it as a text file, read it back in line by line, and save their responses in your table.

Lots of cool stuff in this lesson.

Quiz

Q1. What is the primary topic covered in Microsoft Access Developer Level 30?
A. File input and output using classic VB in Access
B. Advanced form design in Access
C. Table normalization and relationships
D. Advanced SQL query writing

Q2. Why does Richard recommend learning classic VB file input and output before the File System Object?
A. Classic VB is more powerful than File System Object
B. File System Object only works with newer Access versions
C. Classic VB is easier for basic tasks and still widely used
D. File System Object does not support reading text files

Q3. Which two types of file I.O. does Richard mention in the introduction?
A. ADO and DAO
B. Classic VB and File System Object
C. ODBC and OLEDB
D. Excel and Access

Q4. What is the importance of learning recordsets before taking this class?
A. Recordsets are required to create tables
B. Recordsets are used when importing data from text files into tables
C. Recordsets are only used for designing forms
D. Recordsets help to export Access to Excel

Q5. What is mentioned as a limitation that file I.O. can address?
A. Importing files larger than 2 gigabytes which Access cannot handle directly
B. Exporting forms to PDF
C. Creating automatic backups
D. Linking Access to external databases

Q6. Which Access versions are mentioned as being compatible with the material in this class?
A. Only Access 2019 and newer
B. Only Access 2016
C. Access 2003 and newer
D. Only Office 365 online

Q7. What are some actions you will perform with classic VB file input and output in lesson four?
A. Design forms using VB
B. Open, output, print, close and append to text files
C. Write VBA to export graphs
D. Use SQL to import data

Q8. What is the purpose of the Shell command in this class?
A. To link Access and SQL Server
B. To open Notepad and view the exported text file automatically
C. To create a backup of the database
D. To delete temporary files

Q9. How can exported customer data be handled according to the lesson?
A. Only printed out for reference
B. Ignored after export
C. Imported back into the database after being updated externally
D. Only used to create reports

Q10. What is one way surveys are managed using file input and output techniques discussed?
A. Surveys are completed online only
B. Customer responses are pasted into the database and exported, then read back line by line and saved into tables
C. Survey data is entered manually by staff
D. Surveys are not covered in the class

Answers: 1-A; 2-C; 3-B; 4-B; 5-A; 6-C; 7-B; 8-B; 9-C; 10-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 working with file input and output in Microsoft Access. The primary goal is to understand how to read and write text files using classic Visual Basic (VB) file input and output methods. There are many practical reasons to master this skill, and I will be explaining those as we move forward.

Classic VB file input and output is a straightforward way to handle text files in Access, and I still turn to it for most basic tasks because of its simplicity. In the next class, Developer 31, we will cover the File System Object, which is a more advanced tool, but for now, it is important to get comfortable with classic file I/O.

Since this is Developer Level 30, you should have already worked through Developer 29 and the earlier courses. For example, we learned about recordsets back in Developer 16, and today we will be building on that knowledge when we use recordsets to read in data from text files and add it to our tables. I recommend taking the classes in order for your own understanding.

For reference, I am using Microsoft Access as part of Microsoft 365, which is similar to Access 2019. The lessons here go back many versions, though, so even if you are working with Access 2003, you should be able to follow along without issue.

If you have questions about the material in today's class, you can scroll to the bottom of the page you are on right now and post them there. Be sure to take a moment to see if your question has already been asked and answered. If you subscribe on that page, you will receive notifications if anyone posts further questions or comments about the class. If your questions don't relate directly to this class, remember to check the Access Forum to connect with the community there.

Now, let me outline what we will cover in today's lessons.

Lesson one starts with an introduction to file input and output, which is sometimes called file I.O. Here, I explain why learning file I.O. is important, describe the two main types of file I.O. — classic VB and the newer File System Object — and compare the advantages and disadvantages of each.

Lesson two is a free bonus lesson from my TechHelp video series focused on importing data from Excel using CSV files as well as fixed-width text files. This gives you a solid foundation for the topic before we get into more advanced work.

Lesson three is another bonus from TechHelp, which is an extended cut just for members. In this lesson, I demonstrate importing data directly using VBA. This serves as a preview of what's coming up in the main portion of today's class.

We will also talk about reading in text files that are larger than 2 gigabytes, which is more than Access usually allows.

Lesson four begins our hands-on work with classic VB file input and output. We will learn to write data to a text file, using commands to open, output, print, close, and append. This includes a discussion of which folders you can write to, which you have permission to access, and how to use the project path to locate the folder your database is stored in.

In this lesson, we will use the FreeFile function and explore how to launch Notepad from Access to view the text file you just exported. I will also show you how to loop through all the controls on a form, so you can export text from every text box. We will work through exporting a list of customers, then export an invoice and all its detailed line items.

Lesson five continues working with classic VB file input and output, this time focusing on reading text files back into Access. You will learn how to open a file for input, read each line one by one using the Line Input command, and process the file right up to the end. We'll take the customer list exported in the previous lesson and import it back into Access. This feature is especially useful if you want to send a file to, say, a sales rep, have them update it, and then bring their changes back into your database.

I will also show how to handle survey responses. Send a survey to your customers, have them reply via email, then copy and paste it back into your database. By exporting the data as a text file, reading it in line by line, and saving their responses into your tables, you have a flexible way to handle this kind of process.

There is a lot of interesting material in this lesson, designed to give you the tools you need for practical file handling in Microsoft Access.

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

Introduction to file input and output concepts
Types of file I/O: classic VB vs File System Object
Pros and cons of classic VB file I/O
Importing data from CSV and fixed-width text files
Exporting text files using classic VB file I/O
Using open, output, print, close, and append commands
Determining accessible folders for file output
Using current project path for file management
Using free file for file handling
Using shell command to open exported files in Notepad
Looping through form controls to export data
Exporting customer lists to text files
Exporting invoices with line items to text files
Reading text files using classic VB file I/O
Opening files for input and using line input
Reading multiple lines to the end of a file
Importing customer data from text files
Reading and processing survey response files
Storing imported survey data in database tables