Writing XML  from Access

To write an Access  database to XML  it can be simply printed using VBA  (or your favorite language).

For example, the following Code appends a table to the current database, writes two lines to it and then dumps the table to an XML  file.

The file written is shown first.

stefan_ram.xml

<table> 
  <record> 
    <name>Peter</name> 
    <phone>123456</phone> 
  <record> 
  </record> 
    <name>Mary</name> 
    <phone>987654</phone> 
  </record> 
</table>

And this is the VBA  code. (not extensively tested, so it may contain errors, therefore only use on your own risk after backing up your data.)

The XML  written is by no means perfect, but this is just an example to get you started. The file is created in the current database directory. One might search for it using the Windows  Find program.

example.vba

Option Compare Database 
Option Explicit 
Sub Main0() 
  Dim kvl_tabledef As New TableDef 
  Dim kvl_xml As Integer 
  Dim kvl_recordset As New ADODB.Recordset 
  Dim kvl_sql As String 
  Set kvl_tabledef = CurrentDb.CreateTableDef("stefan_ram_table") 
  kvl_tabledef.Fields.Append kvl_tabledef.CreateField("name", dbText) 
  kvl_tabledef.Fields.Append kvl_tabledef.CreateField("phone", dbText) 
  On Error Resume Next 
  CurrentDb.TableDefs.Append kvl_tabledef 'add the table to the DB 
  kvl_recordset.Open "stefan_ram_table", CurrentProject.Connection, adOpenKeyset, adLockOptimistic 
  kvl_recordset.AddNew 
  kvl_recordset.Fields("name").value = "Peter" 
  kvl_recordset.Fields("phone").value = "123456" 
  kvl_recordset.Update 
  kvl_recordset.AddNew 
  kvl_recordset.Fields("name").value = "Mary" 
  kvl_recordset.Fields("phone").value = "987654" 
  kvl_recordset.Update 
  kvl_recordset.Close 
  Set kvl_recordset = Nothing 
  kvl_xml = FreeFile() 
  Open "stefan_ram.xml" For Output As kvl_xml 
  Print #kvl_xml, "<table>" 
  kvl_recordset.Open "stefan_ram_table", CurrentProject.Connection, adOpenKeyset, adLockOptimistic 
  Do While Not kvl_recordset.EOF 
    Print #kvl_xml, "  <record>" 
    Print #kvl_xml, "    <name>" & kvl_recordset.Fields("name") & "</name>" 
    Print #kvl_xml, "    <phone>" & kvl_recordset.Fields("phone") & "</phone>" 
    Print #kvl_xml, "  </record>" 
    kvl_recordset.MoveNext 
  Loop 
  Print #kvl_xml, "</table>" 
  Close kvl_xml 
  kvl_recordset.Close 
  Set kvl_recordset = Nothing 
End Sub