Use the following example to export all the tables in a database to a comma-delimited text format like that shown in Figure 3. You will find this sample under the only button in the details section of the download form called frm_unloadAll. The sample works by first establishing the export folder. In this case, it will create a subfolder called Unload directly under the folder where the database is located. Then a DAO TableDef collection is established, and a loop is used to cycle through all the data tables in the collection. The TransferToText method then exports the table to comma-delimited format. If you open one of the comma-delimited files, it may display in either a text editor or Excel, depending on your file type associations in Windows Explorer. See also why this is important in working out the size of tables in your database
Figure 1 - The form that unloads all tables to text
Figure 2 - All the tables are unloaded to a text file
Figure 3 - Comma delimited csv file produced by the download
The following code sample exports all the tables in a database to a comma-delimited text format. This can be found under the button in Figure 1.
Private Sub unload_all_Click()
' This form requires a reference to
' Microsoft Office 12 Database Engine Object library.
Dim i As Integer, unloadOK As Integer
Dim MyTable As DAO.TableDef
Dim MyDB As DAO.Database, MyRecords As DAO.RecordSet
Dim filen As String, unloadDir As String
' See Microsoft Knowledge Base Article 306144 if you want to
' change the following file type.
Const UNLFILETYPE = ".csv"
Const UNLSUBFOLDER = "unload\"
On Error GoTo unload_all_Failed
unloadDir = GetDBPath_FX & UNLSUBFOLDER
Set MyDB = CurrentDb
If Len(Dir(unloadDir, vbDirectory)) = 0 Then
unloadOK = MsgBox("All tables will be unloaded to a new directory called " & _
unloadDir, vbOKCancel, "Confirm The Unload Directory")
If unloadOK = vbOK Then
' Loop through all tables, extracting the names.
For i = 0 To MyDB.TableDefs.Count - 1
Set MyTable = MyDB.TableDefs(i)
' Create the file name as a combination of the table name and the file type.
filen = unloadDir & MyTable.Name & UNLFILETYPE
If left(MyTable.Name, 4) <> "Msys" And left(MyTable.Name, 1) <> "~" Then
' Not an Access system table.
'Export data in comma-delimited format with column headers.
DoCmd.Echo True, "Exporting table " & MyTable.Name & " to " & filen
DoCmd.TransferText A_EXPORTDELIM, , MyTable.Name, filen, True
MsgBox "Unloaded all tables to ... " & unloadDir, 64, "Unloaded Tables"
' Problems with unloading.
Select Case Err.Number
MsgBox "Error number " & Err.Number & " -> " & Err.Description, _
vbCritical, "Problem unloading tables"
Recovering Data From a Comma-Delimited Text File
Create a new blank database
On the External Data ribbon, Choose Import Text as in Figure 4 and then Import as in Figure 5.
Figure 4 - Import a CSV file
Figure 5 - Import a csv file
Choose delimited text and then make sure that You Choose
Figure 6 - Make sure you import column headers
Then follow the Wizard till the end and on the last window, click Finish.
Now that you have the data loaded into a database, you can append, merge, or replace existing data tables as you see fit. Choosing whether to append, merge, or replace data would be specific to your own data structures and is not within the scope of this book.
Comma-delimited files can prove troublesome if you use them to recover from long-term storage if they are stored without documentation. To alleviate this risk, I recommend that you also store information about the structure of the data in the same location as the text files. Maybe even save a picture of your relationship diagram to help with the recovery of the files.
The download file for this article is here - Import both the form and the module into your database and add a reference in the in the VBA project to
"Microsoft Office 12 Access Database Engine Objects"
Written by Garry Robinson
User Story from Years Ago
Exporting to text files means that, in all likelihood, you still will be able to read the files in 20 years—that's as long as CD-ROMs, tapes, or other such media will still be readable. Moreover, if you think that long-term recovery is unlikely, listen to this story. One project that I was involved with had a database of geological data that cost $50 million to put together. Another company bought the project, did nothing with the data for five years, and in the end couldn't read the backup tapes. When we were asked to help, we managed to recover the text data backups from our tapes, and these were used to build a reasonable copy of the database. We also recovered the database from tapes, but the format was proprietary, and the software that could read the proprietary format was long gone.