<

Export an Access Table as Tab Delimited file

Published on
12,247 Points
5,847 Views
4 Endorsements
Last Modified:
Approved
Community Pick
Here is a quick function that will dump any Access table or query to a Tab delimited file with headers. If you don't want the header, comment out the lines 38 to 47.

I built this function because, historically, Access databases will get corrupted and then have to be imported into a blank new database. Many times the drop down would be missed to check the box to import the existing Export and Import specifications. So then at the end-of-month, quarter, year the export would be tried and it will fail. The reason is that the specification is lost and would have to be rebuilt by hand.

It takes longer to build these custom import and export functions. However, once they are built built the first time, it becomes easier to adapt for the next situation.

I did this with a tab delimited (Chr(9)) file in mind. But you can easily change if it for a comma, pipe "|", or any other character you would like to use. Just substitute the Chr(9) code with either another Chr() code or a quoted value. To find other special characters do a search for "ASCII" in the Access help. It will give you a table of characters to use. If you wanted a double or single quoted export you can change the " & Chr(9) & " to "& Chr(xx) & Chr(9) & Chr(xx) &". Chr(39) is a single quote mark; Chr(34) is a double quote mark.

Just put in the TableOrQueryName as the Table or Query you want to export. Then put in the full file name and path (i.e "C:\Temp\MyFile.txt") as the FileNameAndPath value. That should allow you to dump the data out quickly and easily.

Note that this is built on the DAO reference. If it is missing, go to <CTRL>+<G> --> Tools --> References and select it.

The attached file has an example of an esport form to use it.
Export-Format.MDB
Option Compare Database
Option Explicit
 
Public Function Export_Tab_Delimited(TableOrQueryName As String, FileNameAndPath As String)
'This uses the DAO database reference. IN the VB window (Ctrl+G) go to _
 Tools --> References and select the Microsoft DAO 3.6 Library
 
Dim DB As DAO.Database
Dim RS As DAO.Recordset
 
Dim I As Integer
 
Dim FileNum As Integer
'Dim FileNameAndPath As String
 
Dim OutputLine As String
 
FileNum = FreeFile()
'FileNameAndPath = "C:\MyDirectory\MyFile.txt"
 
Set DB = CurrentDb()                              'Use the current database
Set RS = DB.OpenRecordset(TableOrQueryName )      'actually open the recordset
 
If RS.EOF = False Then
    RS.MoveFirst
Else
    MsgBox "No Data", vbExclamation, "Exiting Fuction"
    Set RS = Nothing
    Set DB = Nothing
    Exit Function
End If
 
'Open the file for output
Open FileNameAndPath For Output Access Write Lock Write As FileNum
I = 0
OutputLine = ""
 
'Output the field names as a header
For I = 0 To RS.Fields.count - 1
    If I > 0 Then
        OutputLine = OutputLine & Chr(9) & RS.Fields(I).NAME
    Else
        OutputLine = RS.Fields(I).NAME
    End If
Next I
 
Print #FileNum, OutputLine
'Debug.Print OutputLine
I = 0
OutputLine = ""
 
'start outputting the data
Do Until RS.EOF
    For I = 0 To RS.Fields.count - 1
        If I > 0 Then
            OutputLine = OutputLine & Chr(9) & RS.Fields(I).Value
        Else
            OutputLine = RS.Fields(I).Value
        End If
    Next I
    Print #FileNum, OutputLine
    Debug.Print OutputLine
    OutputLine = ""
    RS.MoveNext
Loop
 
Close #FileNum
Set RS = Nothing
Set DB = Nothing
 
End Function

Open in new window

4
Author:Jim P.
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.