Introduction

Welcome! Read & Write Text Files in VB

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

Welcome to Microsoft Access Developer Level 30. In this course we will focus on file input and output in Microsoft Access, specifically learning how to read from and write to text files using classic VB file I/O methods. We will discuss why file input and output is useful, the differences between classic VB and file system object techniques, and the pros and cons of each. Lessons include exporting customer and invoice data, reading text files, importing data back into tables using recordsets, and handling surveys or updates from external files. We will cover related concepts like folder access and useful VBA commands.

Navigation

Prev: D30
Next: Lesson 01

Keywords

Access Developer, file input output, text files, classic VB, read text file, write text file, VBA, recordsets, CSV import, fixed width data, append command, FreeFile, export data, CurrentProject.Path, Shell command, survey responses, customer data

Subscribe to Introduction
Get notifications when this page is updated

Intro

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're going to learn how to work with text files. There are lots of reasons why, and I'll 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. However, I think it's important to learn classic VB file input and output too because I still use it, as it's a lot easier for basic stuff.

This is, of course, 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. 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 Access 2003, it should still work just fine. This stuff hasn't 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.

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

Lesson one is an introduction to file input/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. We will then 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's 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/output. We are going to learn how to write out a text file, open, output, print, close, and append commands. We will talk about what folders you can write to and what folders you have access to write to. The CurrentProject.Path will get the folder that the database is in. We will learn about FreeFile, how to use the Shell command to open up Notepad to view your exported text file as soon as it's 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 we will 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 focus of Microsoft Access Developer Level 30?
A. File input and output with classic VB
B. Automating reports
C. Building Macros
D. Working with SQL Server integration

Q2. Which programming method will be introduced in Developer 31?
A. File System Object
B. Classic ASP
C. Access Macros
D. ADO.NET

Q3. Why is it recommended to take Developer Level 29 and earlier classes before this one?
A. They provide essential background, such as recordsets, needed for Level 30
B. To skip advanced content
C. Because Level 30 is optional
D. They cover SQL exclusively

Q4. According to the transcript, is the file input/output method taught in Developer 30 compatible with old versions like Access 2003?
A. Yes, it should work fine
B. No, only works in Access 2019 or newer
C. No, only works in Access 2007 and above
D. Only works in Access 365

Q5. In lesson four, what built-in property is recommended to use for getting the folder location of the database?
A. CurrentProject.Path
B. Application.CurrentFolder
C. GetDatabaseFolder()
D. ThisDatabase.Path

Q6. Which of the following commands is NOT mentioned as part of classic VB text file output operations taught in Lesson 4?
A. SELECT
B. Print
C. Output
D. Append

Q7. What is the main purpose of FreeFile mentioned in the transcript?
A. To get an available file number for opening a file
B. To delete a text file
C. To copy files between folders
D. To lock a recordset

Q8. What is demonstrated with the Shell command in this lesson?
A. Opening Notepad to view exported text files immediately
B. Launching Excel to import CSV data
C. Running an Access Macro
D. Calling an API function

Q9. What skill is practiced by exporting and re-importing customer data via text files?
A. Sharing and updating records between users
B. Encrypting sensitive data
C. Running SQL queries on external databases
D. Linking to live Excel data

Q10. When reading text files back into Access in lesson five, which command is used to read lines of text?
A. Line Input
B. Dir
C. InputBox
D. MsgBox

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

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 all about working with file input and output in Microsoft Access. I'm Richard Rost, your instructor, and in this class, we're going to look at how to read and write text files using classic Visual Basic methods. There are a number of practical reasons you would want to do this, and I'll cover those as we go along.

We'll be focusing specifically on classic VB file input and output today. This involves the basic techniques for reading from and writing to text files, which are often the simplest and most straightforward way to work with external data files. In the next class, Developer 31, we'll turn to the file system object, which is a newer and more powerful tool with additional features. However, I think it's important to cover classic methods first because they are often faster and easier for simple projects and are still very much in use today.

This is Developer Level 30, so you should have completed Developer 29 and all the previous levels. For example, back in Developer 16, we covered recordsets, which are going to come up again in this class when we talk about reading from text files and importing that data into tables. Please follow the course sequence to make sure you don't miss any important concepts.

I'm demonstrating everything in Microsoft Access as part of a Microsoft 365 subscription, which is roughly the same as Access 2019. However, virtually everything in this lesson works in much older versions, even Access 2003, since file input and output hasn't changed in a long time.

If you have questions about anything I cover in this class, just scroll down to the bottom of the page. You can post your questions there. I suggest reading what others have asked first, as you might find your answer right away. Be sure to subscribe so you'll get notified if new questions or comments get posted.

If you have a question that doesn't relate to the content of this class, be sure to check out the Access Forum.

Let's briefly review what I'll cover in today's lessons.

The first lesson introduces file input and output, or file I/O for short. We'll discuss why you should want to learn it and explain the two main types: classic VB and the file system object. I'll walk through the advantages and disadvantages of each.

Lesson two is a free bonus lesson based on a TechHelp video. In this part, I'll show you how to import data from Microsoft Excel, CSV files, and fixed-width text files. This provides a useful foundation for what comes next.

Lesson three is another free TechHelp bonus, this one an extended cut of the previous video. Here, I show how to import data directly into Access using VBA. This serves as a preview for the main techniques we'll be covering in the rest of the class. I'll also touch on handling text files that are too large for Access' standard import tools, such as files greater than two gigabytes.

In lesson four, we'll start hands-on work with classic VB file I/O. I'll show you how to create a text file, write data to it, and use commands like Open, Output, Print, Close, and Append. We'll cover where you can and cannot write files, how to use CurrentProject.Path to find your database location, and the FreeFile function. I'll also show how you can use the Shell command to open Notepad and take a quick look at your exported files. You'll learn how to loop through all the controls on a form to export their values - for instance, exporting all the text box data from a customer entry form. We'll export a customer list, as well as invoices including line item details.

Lesson five continues the classic VB file I/O discussion, this time focusing on reading from text files. I'll walk you through opening text files for input, reading them line by line with the Line Input command, and working through multiple lines until reaching the end of the file. We'll then take the customer data exported in the previous lesson and import it right back in, which is useful if you send a file to a sales rep to update and want to merge their changes back into your database.

I'll also show you how to handle survey responses. You can send out a survey as a text file to your customers, receive their responses, and then import those answers directly into your database by reading the file and updating your tables line by line.

As you can tell, there is a wide range of practical uses for file input and output, and we'll cover many of them in this class.

For a complete video tutorial with all the step-by-step details covering everything discussed here, visit my website at the link below.

Live long and prosper, my friends.

Topic List

Introduction to file input/output (file I/O) in Access
Classic VB vs File System Object for file I/O
Pros and cons of classic VB and File System Object
Overview of CSV and fixed-width text file import
Writing out text files using classic VB
Using Open, Output, Print, Close, and Append commands
Determining which folders you can write to
Using CurrentProject.Path to get database folder
Using FreeFile to manage file numbers
Using Shell to open Notepad for text files
Looping through form controls to export data
Exporting customer data to text files
Exporting invoices with line item details
Reading text files using classic VB
Opening files for input and reading lines
Using Line Input to read lines from text files
Reading multiple lines and detecting end of file
Importing exported customer data back into Access
Sending and reading survey responses via text files
Saving survey responses in a table using file I/O

Article

Welcome to this tutorial on file input and output in Microsoft Access using VBA. In this lesson, we will explore how to read from and write to text files directly from your Access applications. Understanding file I/O is essential for automating data transfers, dealing with large datasets, and integrating Access with other systems. We will focus on the classic Visual Basic (VB) approach to file handling, which is straightforward and efficient for many everyday tasks.

There are two main ways to work with files in VBA: the classic VB methods and the FileSystemObject. Today, we will concentrate on the classic VB techniques, which remain widely used because of their simplicity and reliability. Even though newer tools like the FileSystemObject offer additional features, classic VB file operations are often quicker and easier for basic jobs, such as exporting or importing simple text files.

Before we get started, make sure you are comfortable with the basics of VBA and recordsets. We will use recordsets to add data from text files into database tables. These skills are also useful if you plan to handle larger files, such as those that exceed Access's 2GB file size limit.

File I/O operates on plain text files, which are commonly used for data exchange, backups, and reporting. There are two typical file formats: CSV (comma-separated values) and fixed-width text files. CSV files use commas to separate data fields, while fixed-width files align data in columns of predetermined width. Access can import these formats directly through the import wizard, or you can use VBA for more control and automation.

To export data from your Access database to a text file, you can use the following VBA code. This example writes a simple customer list to a file:

Dim intFile As Integer
Dim strFileName As String
Dim rs As DAO.Recordset

strFileName = CurrentProject.Path & "\CustomerList.txt"
intFile = FreeFile
Open strFileName For Output As #intFile

Set rs = CurrentDb.OpenRecordset("Customers")
Do While Not rs.EOF
Print #intFile, rs!CustomerID & "," & rs!FirstName & "," & rs!LastName
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Close #intFile

This script begins by generating a file name in the same folder as your database. FreeFile grabs the next available file number. The Open statement opens the file for writing, erasing any existing content. After opening a recordset on the Customers table, it writes each record as a line in the text file, separating fields with commas. Finally, it closes the file and recordset.

If you want to append information to an existing file, use "For Append" in the Open command instead of "For Output". This keeps the existing content and adds new text at the end.

To open a text file in Notepad after exporting it, use the Shell command:

Shell "notepad.exe " & strFileName, vbNormalFocus

This opens Notepad and displays your file, making it easy to review what you have exported.

Now, let us look at how to read a text file and import data back into your Access database. Suppose you have a text file structured with customer data, each field separated by commas. The code below demonstrates reading each line from the file and saving the data to a table:

Dim intFile As Integer
Dim strFileName As String
Dim strLine As String
Dim arrFields As Variant
Dim rs As DAO.Recordset

strFileName = CurrentProject.Path & "\CustomerList.txt"
intFile = FreeFile
Open strFileName For Input As #intFile

Set rs = CurrentDb.OpenRecordset("Customers")
Do While Not EOF(intFile)
Line Input #intFile, strLine
arrFields = Split(strLine, ",")
rs.AddNew
rs!CustomerID = arrFields(0)
rs!FirstName = arrFields(1)
rs!LastName = arrFields(2)
rs.Update
Loop
rs.Close
Set rs = Nothing
Close #intFile

In this example, the file is opened for input. The code reads each line with Line Input, splits it into fields using the Split function, and then adds a new record to the Customers table for each line. After all lines are processed, the file and recordset are closed.

This technique allows you to exchange data with coworkers or other companies. For instance, you could send exported customer data to a sales rep, who updates the file and returns it. You can then process the returned file with this script to update your database.

Besides data imports and exports, file I/O in Access is valuable for surveys and data collection. You can build a feature to export a survey for customers as a text file, email it to them, collect responses, and import the results back into your table.

When working with files, keep in mind that you might not have permission to write to certain folders on some computers, especially system directories or the root of the C drive. A safe choice is to use the folder where your database is stored, which you can retrieve with CurrentProject.Path.

To summarize, classic VB file I/O in Microsoft Access lets you automate the process of reading and writing text files. You can easily export data to files, import data from files, and use these techniques to exchange information with others or manage large datasets. Remember to handle files carefully, close them after use, and test your scripts with sample data to make sure everything works as expected.

With these foundations, you can expand your skills into more advanced file handling topics, such as using the FileSystemObject for additional file management capabilities, or handling other file formats. For now, practice exporting and importing text files using these examples, and you will have a valuable new tool for your Access applications.