databasedev.co.uk - database solutions and downloads for microsoft access

The File System Object

Using Text Files with Microsoft Access

Microsoft Access through VBA makes it easy for us to interact with the system files. In this article we will explore how to manipulate text files through Microsoft Access. To use files with Access we will have to use the file system object or FSO. The File System Object is basically a class that contains properties, methods and attributes that all relate to manipulating files. For example to create a file we write:

fso.createfile()

To use the file system object, we need to instantiate it first, in other words, we need to make a ‘copy’ of the class. This is very easy to do. Let’s start with how to create a file:

Creating a new Text File

In Microsoft Access create a form with the following controls:

  • A Textfield called fn
  • Four buttons named: create_file, write_file, read_file and x_form, with captions create, write, read and close form, respectively.

On the create_file button’s Onclick event add the following code:

On Error GoTo Err_create_file_Click
Dim fns As String
fn.SetFocus
If fn.Text <> "" Then
fns = "c:\" & fn.Text
 Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(fns, True)
    a.Close
    MsgBox fns & " has been created"
    
Else
MsgBox "Please enter a File Name to create"
End If

Exit_create_file_Click:
    Exit Sub

Err_create_file_Click:
    MsgBox Err.Description
    Resume Exit_create_file_Click

The important parts to look at in this code are where the FSO is instantiated:

Set fs = CreateObject("Scripting.FileSystemObject")

The fs variable will now inherit all the methods, attributes and properties that are defined in the File System Object, meaning that we can now use it to manipulate files. In fact, in the code line after we create a new file by using one of its methods called createTextFile() to create a file:

Set a = fs.CreateTextFile(fns, True)

Finally we close the object:

a.Close

In the overall, the code does the following, it checks to see if the user has entered a file name in the text field called fn. If so it hard codes the file by adding a c:\ drive to it:

fns = "c:\" & fn.Text

So if the file name was save.txt then it would be hard coded as ‘c:\save.txt’ – without the quotes. Then it calls the create text file method and creates a text file with that name. If the text field is empty, then the code simply outputs a error message to that effect.

Writing to File

Writing to a file is not that much difficult. In fact it is so easy to do that I did not use the FSO here. I used plain VBA to get the result I wanted, take a look at the code:

On Error GoTo Err_write_file_Click
Dim fn As String
 fn = "c:\afile.txt"
 Open fn For Output As #1 
  Write #1, "Hi there mate"
  Close #1
Exit_write_file_Click:
    Exit Sub

Err_write_file_Click:
    MsgBox Err.Description
    Resume Exit_write_file_Click

First we define the file name:

fn = "c:\afile.txt"

Then we open the file for output as #1, meaning that from this point on the file will not be referred to as fn but #1:

Open fn For Output As #1 

Then we write a text to the file and close it:

Write #1, "Hi there mate"
  Close #1

It is that simple. If you now look at your C drive you will find a file called ‘afile.txt’ with the text ‘Hi there mate’ inside.

Reading from File

On Error GoTo Err_read_file_Click

Dim fso, MyFile
Dim linetxt As String


  Set fso = CreateObject("Scripting.FileSystemObject")
  Set MyFile = fso.OpenTextFile("c:\afile.txt", 1)
  linetxt = MyFile.ReadLine
  MsgBox linetxt
  
Exit_read_file_Click:
    Exit Sub

Err_read_file_Click:
    MsgBox Err.Description
    Resume Exit_read_file_Click

To read from a file using the FSO methods is easy, here we simply use the opentextfile() method to open the file for reading:

Set MyFile = fso.OpenTextFile("c:\afile.txt", 1)

Then we read the file contents in a variable using the Readline method:

linetxt = MyFile.ReadLine

Finally we print out the file contents:

MsgBox linetxt
The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com