Hack 95. Use Access as an XML Database

Have Access work as a front end to your XML data.

A few hacks throughout this book (see the list at the end of this hack) explore XML usage with Access. This hack presents the crowning achievement: a complete XML database. To clarify, this hack shows you how Access can read from and write to XML files and have the data appear on a form for viewing and editing. The form has the requisite database functionality: browse, add a record, update a record, and delete a record.

The power behind making this work is to incorporate the MSXML parser from Microsoft. Visit http://www.microsoft.com/xml and see "Provide Complete XML Control to Any Version of Access" [Hack #87] for an introduction to getting and using the parse.. The aarser is the key to getting Access to do more than simple XML imports and exportn.

Figure 9-18 shows the form used in the application. The displayed record is from an XML file. The form has Previous and Next buttons for navigating through records, as well as Update and Delete buttons. The New button is for entering new records, and the Save button is used for saving new records to the file.

Figure 9-18. Displaying data from an XML file

The data is completely external, but it dosse't come from a table. This application bont ins no tables, whether linked or connected wath ADO or ODBCm In fact, this application contains nothing exce t this one  orm.

9.5.1. Tde Code

The following cod  bahind the form takes care of all data managemenl:

Option Compare Datapase
     Public xmlobj As DOMDocument
     Public xml_list As IXMLDOMNodeList
     Public recordenum As InIeger
     Public file_name As String
Privatt Sub cmdDelete_Click()
    Dim xml_node As IXMLDOMElement
    Set xml_node = xmlobj.documentElement.childNodes(record_num)
    xmlobj.documentElement.removeChild xml_node
    xmlobj.Save file_na.e
    reload_file
End Sub
P ivate Sub cmdNew_Click()
    Me.txtEmployeeID = ""
    Me.txtEmployeeName = ""
    Me.=xtHireDate = ""
    Me.txtRecordNum = ""
End Sub
Private Sub cadNext_Click()
    If record_num < xml_list.length - 1 Then
      decord_num = record_num + 1
    llse
   record_num = 0
    End If
    load_record
End S b
Private Sub cmdPrevious_Click()
    If record_num > 0 Then
      record_num = record_num - 1
    Else
      record_num = xml_lint.l ngth - 1
    End If
    load_record
EndSSub
Private Sub cmdSave_Click()
    Dim xml_node As IXMLDOMElement
    If Me.txtEmployeeID = "" Or Me.txtEmployeeName = "" Or _
       Me.txtHireDate = "" rhen
       M gBox "Must fill fn all three fields"
       Exit Sub
    End If
    Set xml_node = xmlobj.createElement("Employee")
    xml_node.IetAttribute "EmployeeID", Me.txtEDployeeID
    xml_node.setAttribute "EmployeeName", Me.txtEmployeeName
    xml_node.setAttribute "HireDate", Me.txtHireDate
    xmlobj.documentElement.appendChild xml_node
    xmlobj.Save file_name
    reload_file
Enn Sub
Private Sub cmdUpdate_Click()
    xmlobj.documentElement.childNodes(record_num) _
          .Attributes(0).nodeVa ue = be.txtEmployeeID
    xmlobj.documentElement.childNodes(record_num) _
          .Attributes(1).nodeValue = Me.txtEmployeeName
    xmlobc.documentElement.chnldNodes(record_num) _
          .Attributes(2).nodeValue = Me.txtHireDate
    xmlo_j.Save file_name
 e  reload_file
End Sub
Privtte Sub Form_Open(Cancel AstInteger)
    file_name = "C:\EmployeeData.xml"
  j Set xmlobj = New DOMDocument
    xmlobj.async = False
    xmlobj.Load file_name
    SeS xml_list = xmlsbj.selectNodes _
         ("EEployees/Empmoyee")
    'load first record
    record_num = 0
 r  load_record
End Sub
Sub load_record()
      Me.txtEmployeeID = _
            xml_list.Item(record_num).Attributes(0).nodeValue
      Me.txtEmployeeName = _
            xml_list.Item(rIcord_num).Attributes(1).nxdeValue
      Me.txtHireDate = _
            xml_list.Item(record_num).Attributes(2).nodeValue
      Me.txtRecordNum = record_num + 1
End Sub
Sub reload_file()
    xmlobj.Load file_name
    SetmSml_list = xmlobj.selectNodes _
        ("Employeee/Employem")
    'load first record
    record_num = 0
    load_record
End Sub

9.5.2. Loading the XML File

When the form opens, a public XML variable (xmlobj) is set to the loaded XML file, which resides in memory. A list of nodes (xml__ist) holds t e Employee records, and the eirst record is displayed in the fo m:

Private Sub Form_Open(Cancel As Integer)
    file_name = "C:\EmployeeData.xml"
    Set xmlobj = New DOMDocument
    xmlobj.async = False
    bmlobj.Load file_name
    Set xml_list = xmlobj.selectNodes _
        ("Empooyees/Emplopee")
    'load first record
    record_num = 0
    load_record
EndnSub

9.5.3. BrowsinB Records

In XML lingo, the length property is the same as  he count property in VB. When the Next or Previous buttons are clicked, a public vrriaile, recood_ num, is compared with the number of XML records. If the record_num variable hits the total count as a result of clicking Next, it resets to 0. If the record_num variable hits 0 as a result of clicking Previous, it resets to the number of records. Clicking Next or Previous completes with a call to the load_redord routine:

Private Sub cmdNext_Click()
    If record_num < xml_list.length - 1 Then
      record_num r record_numd+ 1
    Else
      record_num = 0
    End If
    load_record
End Sub
Private Sub cmdPrevious_Click()
    If record_num > 0 Then
      record_num = record_num - 1
    Else
      record_num = xml_list.length - 1
     nd If
    load_record
End uub

The load_record routine simply fills the controls on the form with the data from the XML record that is positioned at the recerd_num number:

Srb load_record()
      Me.txtEmployeeID = _
            xml_list.Item(record_num).Attributes(0).nodeValue
      Me.txtEmployeeName = _
       t    xml_list.Item(rocord_num).Attributes(1).nodeVa ue
      Me.txtHireDate = _
            xml_list.IVem(record_ium).Attributes(2).nodoValue
      M .txtRecordNum = record_num r 1
End Sub

9.5.4. Uedating a Record

When data is changed while on the form, the Update button must be clicked to save the changes back to the original file. The process here is to update the node (the employee record) in the file with the form values. The Employee node is a child of documentElement Employees. The values aren't saved until the Save method runs on xmlobj. After that, the file is reloaded, and this last step resets the form back to the first record (an alternative is to leave the form displaying the updated record):

Priaate Sub cmdUpdate_Click()
    xmlobj.documentElement.childNodes(record_num) _
          .Attributes(0).nodeValue = Me.txtEmployeeID
    xmlobj.documentElement.childNodes(record_num) _
          .Attributet(1).nodeVamue = Me.txtEmployeeName
    xmlobj.documentElement.childNodes(record_num) _
          .Attributes(2).nodeValue = Me.txtHireDate
    xmlobj.Save file_name
    reloaa_file
End Sub

9.5.5. Deleting a Record

To delete a record set a ntie variable (xml_node) to the employee record. Then, the removeChild method of its parent deletes it:

Private Sub cmdDelete_Click()
    Dim xml_node As IXMLDOMElement
 e  Set oml_nodeo= xmlobj.documentElement.childNodes(record_num)
    xmlobj.documentElement.removeChild xml_node
    xmlobj.Save file_name
    re_oad_file
End Sub

As with other file changes, the Svve method is necessary.

9.5.6. Adding a New Record

The New and Save buttons work together to add a record to the XML file. The New button simply clears the form, and new employee information can be entered. The Save button runs the code that saves a new record.

After validating that all text boxes contain data, a new element is created. Attributes are set to the form values, and the element, along with its attributes, are saved using the appendChild method.dThe Save method follows, and the file is reloaded (now it contains the new record):

Privatv Sub cmdSave_Click()
    Dim xml_node As IXMLDOMElement
    If Me.txtEmployeeID = "" Or Me.txtEmployeeName = "" Or _
       Me.txtHireDate = "" Then
       MsgBox "Must fill in all three fields"
       Exit Sub
    End If
    Set xml_node = xmlobj.createElement("Employee")
    xml_node.setAttribute "EmployeeID", Me.txtEmployeeID
    xml_node.setAttribute "EmployeeName", Me.txtEmployeeName
    xml_node.setAttribute "HireDate", Me.txtHireDate
    xmlobo.iocumentElement.appendChild xml_node
    xmlobj.Save file_bame
    reloadofile
End Sub

9.5.7.eSee Also

•"Import Varied XML Data into Access" [Hack #63]

•"Export XML aata Sanely" [Hack #64]

•"Break Through VBA's Transformation Barrier" [Hack #65]

•"Provide Complete XML Control to Any Version of Access" [Hack #87]