Saving Tables to Text Files

This section explains the benefits of exporting data into text files and the methods of recovering that data into a (new) database. At anytime of the day, you can perform "live" extracts of all the information from a table to a text file. Exporting to text files is a good idea for the following reasons:

• You can generally do it at any time of the day, whether or not users are in the database. Only the currently saved records will be transferred. Keep in mind, though, that it would be better to rely on information that this software extracts when everyone has logged off, because getting information only from saved records may cause concurrency and contention issues.

• Long- term storage of data in text files will allow a DBA to read the information into multiple products. These text files probably will require accompanying documentation in a simple format so that the person who recovers the data does not have to guess the design of the tables that have been exported.

• Data exports are also a good idea to do prior to adding protection, such as passwords, to your databases.

Right from my early days as an Informix DBA, I learned that it was a good idea to export the tables in databases to text files. I like to do this so that I always know that a DBA looking at the text backups sometime in the future will be able to read the text files by using a text editor and import them into another system. What is more problematic is that you may not have a program that can read the database. As an example, contemplate how you would read information from an Access 97 database that you stopped using in 2002. When you pull the database from the archives and try to open it in 2008 by using the latest version of Access, you may unfortunately find that it doesn't support the Access 97 format. If you think that is unlikely , Microsoft has scheduled to remove Access 97 from its Web site sometime after the start of 2005. Though I don't imagine that Microsoft will drop support for Access 97 files for a long time, you never know. For more details on the support timetable for Microsoft products, see the links in the "Further Reading" section at the end of this chapter.

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.

User Story  

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.

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 

Caution  

Not all exported tables will import into Access successfully from the comma-delimited format. This does not mean that there is any problem with the file. Generally, it means that there is a problem with the system that Access uses to predict what the field type of a column is in these external files. For example, when a decimal field is blank for the first 20 lines of the unload file, decimal number columns can be incorrectly classified as integer numbers. Once that incorrect classification has been made, for the rest of the import process, the decimal numbers are rounded to integer values. Also, some text fields can contain unusual characters (like ' or "or foreign languages enunciations), which will unload from the tables with unusual results. Generally, though, this type of data exporting is worth trying first because it is simple and because most systems will read comma-delimited files.

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.