Saving Tables as Comma-Delimited Text Files

To demonstrate how you can export tables to text, use the following example to export all the tables in a database to a comma-delimited text format like that shown in Figure 5-6. You will find this sample under the only button in the details section of the download form called frmGR8_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.

Figure 5-6: The exported comma-delimited file viewed in a text editor.

The following example exports all the tables in a database to a comma-delimited text format.

Private Sub unload_all_Click()

' This form requires a reference to
' Microsoft DAO 3.6 or 3.51 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
      MkDir unloadDir
    Else
      GoTo unload_all_Final
    End If
  End If

  ' 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

    End If

  Next i
  MsgBox "Unloaded all tables to ... " & unloadDir, 64, "Unloaded Tables"

unload_all_Final:
  Exit Sub

unload_all_Failed:
  ' Problems with unloading.
  Select Case Err.Number
    Case Else
      MsgBox "Error number " & Err.Number & " -> " & Err.Description, _
       vbCritical, "Problem unloading tables"

  End Select
  Resume unload_all_Final:

End Sub

In the preceding code, a function called GetDBPath_FX helps to establish a subdirectory below where the current database exists.

I have made previous references to backups only being as good as the recovery process, so I now will show you how to recover a comma-delimited text (.CSV) file.

Recovering Data From a Comma-Delimited Text File

In the following example, I will show you how you can recover data from a comma-delimited text file. The steps to recover the data from the categories.csv file (shown previously in Figure 5-6) follow.

1. Create a new blank Access database by choosing File Ø New Database.

2. Choose File Ø Get External Data Ø Import.

3. Choose files of type Text Files (*.TXT, *.CSV, *.TAB, or *.ASC) and click Import.

4. Navigate to the correct folder by using the Look In box and select the file.

5. On the first window of the Import Text wizard, choose Delimited and then click Next.

6. On the second window of the wizard, make sure Comma is selected as the type of delimiter, select the First Row Contains Field Names check box (as shown in Figure 5-7), and then click Next.

   
   Figure 5-7: The second window of the Import Text wizard.

7. On the third window of the wizard, choose to load the file into a new table.

8. Accept the Access defaults by using the Next buttons, 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. Thankfully, there is now a popular new text standard called XML that will provide a more documented format for long-term storage of text files. I will now explain how you can instruct Access to use that format.

Using Access 2002 XML Methods to Export Tables

As I explained in the section on backing up to comma-delimited text files, there are some issues with recovering data from that format. A better way to back up tables to text files is by using the eXtended Markup Language (XML). Access 2002 provides a good platform to get involved in XML because you can easily save or retrieve tables and queries from the XML format by using the ExportXML and ImportXML methods of the application object. In every XML file that Access generates, you will find not only the data but descriptions of the structure of the data as well. This addition is an improvement on such text files as the comma-delimited format because there is no more guessing whether a field is text or integer or decimal. Figure 5-8 shows an XML file created from the Northwind Categories table.

Figure 5-8: The Shippers table from the Northwind database in XML format.

In the VBA example provided in the sample database, the procedure shown in the following code will export all the tables in the database to XML files. In this instance, I use the ADO Extensions for DDL and Security library (ADOX) to provide a list of all the tables in the database.

I use ADOX in this example because the database object types have properties that allow you to establish easily whether a table is linked or is normal (nonsystem). As the loop progresses to each table in the tables catalog, the ExportXML method is used. The code under the only button on the form called frmUnload2002xml demonstrates this.

Private Sub cmdUnlXML_Click()

' Set a reference for
' Microsoft Active X Data Object 2.5 library.
' Microsoft ADO Extensions 2.5 for DDL and security.

Dim tblsExported As String, cancel As Integer
Dim objT As ADOX.Table, objV As ADOX.View
Dim io As Integer, unloadOK As Integer
Dim adoxCat As ADOX.Catalog
Dim filepath As String
Dim xmlFolder As String, tableName As String
Const REBUILDFILE = "_RebuildMdbTables.txt"
Const INCLUDESCHEMA = 1

xmlFolder = GetDBasePath_FX & "backupXml\"
If Len(Dir(xmlFolder, vbDirectory)) = 0 Then

  unloadOK = MsgBox("All tables will be unloaded to a new directory called " & _
    xmlFolder, vbOKCancel, "Confirm the Unload Directory")
  If unloadOK = vbOK Then
    MkDir xmlFolder
  Else
    GoTo cmdUnlXML_Exit
  End If
End If

io = FreeFile
Open xmlFolder & REBUILDFILE For Output As io

Print #io, "public sub RebuildTables"
Print #io, ""
Print #io, "' Generated by software written by Garry Robinson"
Print #io, "' Import this into a blank database and type"
Print #io, "' call RebuildTables "
Print #io, "' into the Immediate Window"
Print #io, ""
Print #io, "msgbox ""This will a load a number of XML files into new tables. "", _"
Print #io, ", vbInformation"

Print #io, ""

On Error Resume Next
Set adoxCat = New ADOX.Catalog

adoxCat.ActiveConnection = CurrentProject.Connection

tblsExported = "Tables that were exported to " & xmlFolder & vbCrLf & vbCrLf
txtXMLFile.Visible = True
For Each objT In adoxCat.Tables

  If objT.Type = "Table" Or objT.Type = "Link" Then
    ' Queries are ignored in the exporting process.
    tblsExported = tblsExported & objT.Name & vbCrLf
    tableName = objT.Name
    DoCmd.Echo True, xmlFolder & tableName & ".xml"
    filepath = xmlFolder & tableName & ".xml"
    ' Export the table to an XML file.
    txtXMLFile = tableName
    ' Save the table as an XML file.
    ExportXML acExportTable, tableName, xmlFolder & tableName & ".xml", _
     , , , , INCLUDESCHEMA

    Print #io, "importXML """ & filepath & """, acStructureAndData"
  End If
Next objT

On Error Resume Next

Print #io, "msgbox ""End of table import from XML"""
Print #io, ""
Print #io, "end sub"
Close io
MsgBox tblsExported, vbInformation, "End Of Exports"
Set adoxCat = Nothing

cmdUnlXML_Exit:
End Sub

One good thing about the implementation of XML in Access 2002 is that you can export and import by using XML without truly understanding the format itself. The XML exports and imports work well because they correctly handle issues like unusual characters and bitmaps. In the next section, I show you how you can recover your XML files.

Recovering Data From an Access 2002 XML File

You can use the Import command on the File menu to recover a table that has been exported to XML. Notice that when you import an XML file into a database that already has a table with the same name, you will create a table with the same name plus a numerical suffix, such as TableName1. The process for importing a single XML file in Access 2002 or later follows:

1. Choose File Ø Get External Data Ø Import.

2. Choose XML documents (*.XML, *.XSD) from the Files of Type drop-down list.

3. Choose the XML file, then click Import, and then click OK.

4. Be wary of choosing the Append to Existing Data option because you will need to check that all the records are new before you load them.

This process will work for a few files, but if you want to recover a number of tables, you may want to automate this process by using the VBA code recovery file generated by the preceding piece of code (found in form frmUnload2002xml). As part of the export process, this form generates a table recovery file (show in Figure 5-9) that you can use to load all the XML files into a blank database.

Figure 5-9: The text file that can reload the tables into a blank database.

Here are the steps that will rebuild all the tables from the Access 2002 XML backup process:

1. Create a new blank Access database by choosing File Ø Create Database.

2. Open the Visual Basic Editor by pressing ALT+F11.

3. Choose File Ø Import File.

4. Choose all file types (*.*).

5. Find the table recovery file called _RebuildMdbTables.txt and click the Open button.

You've now created a module in your blank database called Module1. Review the code in this module. Now you can open the Immediate window by pressing CTRL+G or by choosing View Ø Immediate Window. Type the following into the Immediate window to reload the tables:

call RebuildTables

If you are using Access 2000 or even Access 97, you can take advantage of XML by using the ADO 2.5 library to generate the XML. The next section describes a form that will show you how to do this.

Using ADO to Generate XML Files in Access 2000

Long before I developed the automated XML export code discussed in the section "Using Access 2002 XML Methods to Export Tables," I used ADO 2.5 to export tables to text files. Using XML in this way isn't anywhere near as integrated into Access as the ExportXML method in Access 2002, but it does provide a way to generate XML files for backup. If you want to try this particular approach, open the demonstration database for Access 2000 (grMap2000.mdb) and choose the form called frmUnloadADOxml. This form has all the necessary code under the one button and is very similar in structure to that code discussed earlier in the chapter and demonstrated in the section on using Access 2002 XML methods to export tables.

The following code snippet illustrates how the ADO Recordset object's Save method saves a table to an XML file. Before exporting a database, you must delete the existing XML file by using the Kill function. The code snippet provided requires that you upgrade to at least version 2.5 of the ADO library (Microsoft ActiveX Data Object 2.5 library) and include this reference in your Access application.

If this method of exporting appeals to you, you can also use a proprietary and more compact Microsoft format to store the exported information. Select this format by changing the adPersistXML constant in the second argument of the Recordset object's Save method to use the adPersistADTG constant. This adPersistXML format may even appeal to you as a security precaution because the file format is binary and cannot be read in a text editor. Another advantage of the adPersistXML constant is that it will allow you to use the ADO 2.1 library that comes with Access 2000.

  Set rst = New ADODB.RecordSet
  rst.ActiveConnection = CurrentProject.Connection
  datasource = objT.Name
  rst.Open datasource

On Error Resume Next
  Kill cachedir & datasource
  rst.Save cachedir & datasource & ".xml", adPersistXML
  Set rst = Nothing

Now that you have seen a number of ways to save a table to a text file, the next section will show you how to save code and objects to text files and then recover the objects at a later date if necessary.