Backing Up Your Access Tables With XML  

By Garry Robinson                            

Have you ever had an Access database give up the ghost and end up in a state where some data was unrecoverable . I’ve had a few and I can only guess that many of readers may have been in the same situation. Well the reason for this is that you have relied on a (binary) file format that is only readable by a particular piece of software such as the Microsoft Access program. And don’t you worry, I have only belatedly accepted this fate after running the repair utility, imported everything into a clean database and any other little trick that I can think off.

So one of the little tricks that I have deployed ever since my early days as an Informix developer, was to include a some sort of software to export all the tables in the database to text files. For these databases, this was really only a 90% effort as the comma delimited files that I used for storing the data had no intelligence about the structure of the data that was being exported. Also in Access, the export to comma delimited format can be a little problematic as it can round decimal data into integer data upon exporting in some situations. Another really good thing that comes of exporting your data to text is that at a later stage (say 5 years) down the track, when you finally realize that you actually needed that data after all, guess what you have no software that will read it. Exporting to text files means that in all likelihood, you still will be able to read the files in 20 years time. That’s as long as CD-ROMs and tapes are still readable. And if you think that is unlikely, one project that I was involved had a database of geological data that cost 50 million dollars to put together. Another company bought the project and did nothing with it for many years and in the end couldn’t read the backup tapes. Our company recovered the text data backups from our tapes and the software that could read the binary copies of the database was long gone.

But the here and now is that this article describes a self contained Access form that you can readily import into your database. This form will then export every table in the database into XML text files in a subdirectory. Thanks to the self contained nature of these XML files, these text files not only will hold all the data but will also describe adequately the structure of the data tables themselves. This then will provide a more robust text format for storing your important information.

  Your Sample Database Is Called   "xmlBackup.zip"

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

The accompanying database called ExportToXML2000.mdb has two forms inside it that you can import into your own application. In this application, I have linked to all the tables in the Access 2000 Northwind database. To help you to re-linking to your location of Northwind, I have included a form that I utilise in most of my new Access front-end databases. This form has the instructions for re-linking and a button on it fires up the table linker add-in that is supplied with Access using the sendkeys command as follows

SendKeys "%TDL"

For Access 97, the command is

SendKeys "%TIL"

Exporting All Tables To XML format

All the source code that you need for backing up your database to XML is included in the form called "frmUnloadToXML". Figure 1 shows the form in action.

To run this form is pretty simple, click on the "unload to XML Now" button and all the tables in the database will be exported to a subdirectory under your software database location called Backup. The hardest bit about installing this form in your database is that you will probably need to add 2 references to your Access project. The references that you will need are

Microsoft Active X Data Object 2.5 Library
Microsoft ADO Extensions 2.5 for DDL and Security

Figure 1. A Form To Export All Linked Northwind tables.

Why Backup Your Tables Using An XML File

To refresh your memory of what the exported XML file looks like, see the following extracts of the XML export of the Northwind Orders table. At this stage let me inform you that I am not an XML expert. All I have learnt so far is how easy it is to use ADO to save a table as an XML file. And here is the first important bit of the XML file as far as the text backups go. The following XML tells us that field 4 in the recordset is called OrderDate and that it is a date field that allows null values. Now that is a lot more information than a fixed width or comma delimited file will give ever give you.

<s:AttributeType name="OrderDate" rs:number="4"
rs:nullable="true" >
<s:datatype dt:type="dateTime" rs:dbtype="variantdate"
dt:maxLength="16" rs:fixedlength="true" />
</s:AttributeType>

Now we move on down to the data section of the XML file and here you will find a row of data from the table is described using z:row tag. There are two things to note here. The first is that this file is quite easy to read and the second is that this would have been quite hard to write using your own software. But that is where the ADO helps out.

<rs:data>
<z:row
OrderID="10248"
CustomerID="VINET"
EmployeeID="5"
OrderDate="1996-07-04T00:00:00"
RequiredDate="1996-08-01T00:00:00"
ShippedDate="1996-07-16T00:00:00"
ShipVia="3" Freight="32.38"
ShipName="Vins et alcools Chevalier"
ShipAddress="59 rue de l'Abbaye"
ShipCity="Reims"
ShipPostalCode="51100"
ShipCountry="France" />

So in summary, an XML file provides a far more comprehensive description of your data than either fixed width or comma delimited text files. Consequently this a more robust format to use to backup your data externally from your database.

The Export All Tables To XML Function

The software that is demonstrated has been written in Access 2000. To convert it to Access 97, you would need to provided your own connection string for the current database. You would also have to be quite careful with your recordset visual basic and the priority of your external libraries when mixing DAO and ADO libraries. To emphasize this, the following code snippet illustrates the data definitions for code behind this form. For example , recordset is declared as "Dim Rst ADODB.recordset" rather than "Dim Rst as Recordset". If you do not include the complete declarations of these variable types, you are going to run into problems as the DAO and ADO libraries deploy objects with the same names. Thereafter, if you use a shared name such as recordset, the library that is highest in the priority order that uses that object name will be used first. This may not be the library that you were thinking about and debugging the problems is very painful.

Dim cboStr As String, cancel As Integer
Dim objT As adox.Table, objV As adox.View
Dim strConnect As String
Dim adoxCat As adox.Catalog
Dim Rst As ADODB.RecordSet
Dim fso, folder
Dim cachedir As String, datasource As String

strConnect = CurrentProject.Connection
Set adoxCat = New adox.Catalog

Note that when programming using ADO in an Access database, I will sometimes get an error message saying the "Database has been placed in a state by user Admin that prevents it from being opened or locked." To fix this during development, I find that I have to compact or close the database down and open it again. If anyone knows of a better way to handle this, please let me know.

adoxCat.ActiveConnection = strConnect
On Error GoTo 0

Now the software loops through all the Tables and in the process ignores both the system tables and queries which we do not need to export for backups. Note that ADO catalog collection labels all non action queries in an Access database ( eg "select * from table") as Views and bundles them up in the ADOX tables catalog. Therefore we need to skip the all the Views

For Each objT In adoxCat.Tables
  If left(objT.Name, 4) = "mSys" Or _
   left(objT.Name, 1) = "~" Or _
   left(objT.Name, 4) = "Usys" Then
    GoTo NotATable
End If

' Before adding the table to combo box string make sure that the
' table isn't a view as we will add these later.

if objT.Type <> "VIEW" Then

Now its time to utilize the File System Object to generate a sub directory folder called Backup under the directory where the Access front-end database exists. To find the current directory, I employ a great little function called GetDBPath that came courtesy of Mike Gunderloy and Issue 4 of the Smart Access Newsletter. This function is illustrated in full as follows

Function GetDBPath() As String

Dim strPath As String
Dim intLastSlash As Integer

strPath = CurrentDb.Name
For intLastSlash = Len(strPath) To 1 Step -1
  If Mid(strPath, intLastSlash, 1) = "\" Then

    Exit For
  End If

Next intLastSlash

GetDBPath = left(strPath, intLastSlash)

End function

Now the following code snippet will generate a subdirectory under the location of the Front End database. If the subdirectory already exists, the method fails and the code just rolls on as per normal.

On Error Resume Next

cachedir = GetDBPath & "backup\"
' Open the scripting object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.createFolder (cachedir)

Finally we only require a very small amount of vb plus the Save method of the ADO recordset object to generate an XML text file for the current Table. Before exporting, it is necessary to delete the existing XML file if it already exists. If you do not wish to add ADO version 2.5 to your Access application, you can use the adPersistADTG constant rather than adPersistXML to save your recordsets in the faster, more compact (but binary) recordset format. This kind of defeats the readability of the files generated by this backup software. 

  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
End If
NotATable:

Next objT

Summing Up The XML Backup

That’s all the code you need to save all the tables in your database to XML format. I hope this will get you out of a major data recovery issue at some time in the future. Remember that this code is written using the Universal ADO data handler which means that you can easily adapt it for your SQL server or Oracle databases if you like. For a thorough description of ADO/XML exporting and importing, Michael Corning described this process very well in March 1999 edition of Smart Access.

Building A List Of Tables And Views For A Combo Box

When I first wrote this code, it was actually developed for generating a list of Tables and Views (select queries) that I could pass to a combo box. As this can be quite useful for Access programmers, I have kept this code in the software so that you can also generate a combo box that displays the list. Figure 1 demonstrates the combo box in action.

The string becomes visible by making the "row source type" property a "values list" and allocating the following software generated text string to the "rowSource" property of the combo box.

"Table/View Name;Type;Categories;table;Customers;table;Employees;table;Order details;table;Orders;table;Products;table;Shippers;table;Suppliers;table;zWorld;table;

I am happy to inform you that building this list of tables and queries is a lot simpler now than the same code was when I wrote it using the DAO object. It is good to find positive reasons for adopting ADO in your Access application and this and XML exporting are definitely amongst those.

 

Useful Further Reading and Resources

Valid and Well-Formed XML in the March 1999 edition of Smart Access

http://www.microsoft.com/data/ is the place for your latest MDAC/ADO download

http://www.xmldevelopernewsletter.com/ is where you can find out more about XML.

A User Called John Notes:

(1) If the database whose tables are being exported is secured, this line will fail:
        strConnect = CurrentProject.Connection
To fix this, you've got to tack on a password parameter to the Connect string. I just set up a prompt to "please re-enter your password," so the line now looks like this:

        strConnect = CurrentProject.Connection & ";Password=" & strPWord

(2) You have to use care if the tables are linked, to ensure that you're exporting ONLY the data you really want to export. In my case, the DB links to a bunch of mainframe Oracle tables, via ODBC -- these are HUGE tables, which the DB application accesses only very selectively, so no way am I going to export those as well. I can think of a couple possible workarounds, e.g. inspecting the Connect string to be sure the function looks only at Access tables.

Author Bio.

Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies web site at http://www.gr-fx.com/ or ssign up for his Access email newsletter here. The web site features a popular shareware data mining tool written in Access. When Garry is not sitting at a keyboard, he can be found playing golf or Greco-Roman Wrestling at one of Sydney's Olympic venues. Contact details … access@gr-fx.com +61 2 9340 7789

Other Pages On This Site You Might Like To Read

Samples of how to backup and recover your Access database
Exploring Your Data With Subdatasheets
The Access Workbench has database backup facilities

 

Click on the following button to jump to the next page in the document loop.