How to export data from access to a text file

Introduction

Hi all
Today's post is about a set of functions to export data from Access to text files.
I wrote the next functions years ago, when working on a big project and I had to export some of my data to another system, by text files, regularly. I needed something easy for the users, and transparent.
I will explain what I did, and how.

At the end of this post you will find a link to download the example of code. Feel free to modify it.

Sometimes it happens that we need to export data. Of course, almost always we may write to another database.

As I said before, I wrote this code to cover a need. This is a brief:

I've been working for a company who mades canned fish. They had their ERP software, made by a external firm, and mantained across more than 20 years. When I entered, I had to write a software to control the production process and traceability.

Of course, my processes (my software) was a subset of all the other software, and they had to communicate them. The other software is wrote using a server running THEOS, an ancient text mode OS.
The unique communication possibility was by plain text files, like csv (Comma Separeted Values).

What we need

To do the exports we need a data source, a table or a query. I will do a simple example.

We will work with a single function with those 2 mandatory parameters, and other optionals, in this case, the separation string.

The function will bi written in a Module, it's better like this, because it may be used by several processes.

The code

This is the code:

Public Sub ExportData(SourceObject As String, DestinationFile As String, Optional strSeparator As String)

    Dim myTable As Recordset

    Dim myField As Integer, nFields As Integer

    Dim strLine As String

    

    If IsNull(strSeparator) Or (strSeparator <> vbTab And strSeparator <> ";") Then

        strSeparator = ";"

    End If

    

    Kill DestinationFile

    

    Open DestinationFile For Output As #1

    Set myTable = CurrentDb.OpenRecordset(SourceObject)

    myTable.MoveFirst

    nFields = myTable.Fields.Count

    While Not myTable.EOF

        strLine = ""

        For myField = 0 To nFields - 1

            strLine = strLine & myTable.Fields(myField) & IIf(myField = nFields, "", strSeparator)

        Next

        Print #1, strLine

        myTable.MoveNext

    Wend

    Close #1

    myTable.Close

End Sub

Analysis of the code

The parameters

• SourceObject: this is a string with the table name, the query name or a SQL string describing a query.
• DestinationFile: this is the name of the file where the data will be written.
• srtSeparator: this is the string for separate the fields in the text file. It may be a semicolon (;) or a Tab. If no one of those possibilities is given, the program will write a semicolon.

The variables

• myTable is the recordset (table in memory) acting as data source
• myField is a number used to identify the current field of the recordset, from 0 to the numbers of total fields (see later)
• nFields is the total number of fields on the recordset
• strLine is a string to store all the data in the record, field by field, and with the separators. I will explain later why.

The process step by step

1. Declare the variables in the Dim section. I always do it like this, it helps me to see better the code.
2. Set the separator. If is null or different than vbTab (tab, given by the VBA constant vbTab) or semicolon (;), let it be semicolon.
3. Delete the previous file. By default. We may see in next posts how to manage this in a better way, or, at least, more complete.
4. Open the file for Output, that is, to write something.
5. Assign the table, query or any object given as a parameter to the recordset and opens it to work
6. Move first. I do this always, to start working always by the first record.
7. nFields = myTable.Fields.Count . I think it does not need more information, set to nFields the amount of fields in the table. We will use this parameter later to know in which field we are reading.
8. And the loop, while not end of recordset. We may think into a list, and here we will read it from top to bottom.
1. Set strLine = "", we renew this value each record
2. Now, the second loop. For each row, here we will read it from left to right
1. Here we say, set strLine with the previous value of itself, after the contents of the field given by myField, and after add, if not the last field, the separator
3. After finishing the row, Print will write the contents of strLine in the file, and after, by default, it will insert a CR, that is, next line.
9. Close the file. This is important.
10. Close the recordset

Summary

This is a very simple function to write data. Of course it may be more and more complicated, like this is useful to export text data and integers.

Obviously from this general idea, you can expand it to your needs.

How to use it?

In this link you may download the file. This week has been tested before upload.

It may be saved on a Module. As the function is declared as Public, it may be called from any procedure or function, for example, in an event procedure from a button on a form.

Hope you like it. Feel free to share and comment this post.