Community Pick: Many members of our community have endorsed this article.

Export an Access Table as Tab Delimited file

Published:
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
6,760 Views

Comments (2)

Most Valuable Expert 2014

Author

Commented:
You mean :

'FileNameAndPath = "C:\MyDirectory\MyFile.txt"


That is rem code for testing. I assume a valid from the end-user. I could probably build the FSO system, but that is not relevant for the code I'm posting.

Commented:
Jim, I am a highly unsophisticated ACCESS user but I could really use this function. A couple of questions:
1) When I download the db and run the form, I get an error "MS database engine could not find the input table or query 'MyQueryName'. Make sure that it exists and that it's name is spelled correctly." So I can't get it to work in it's initial db. How do I fix this?

2) If I want use this in another db, can I just export the Form and the function to the other database? If not, what is needed to do that?  This way I don't have to code anything (I hope).

Thanks,
Ed

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.