Avatar of Dustin Stanley
Dustin Stanley

asked on 

Need help with adding Child and Granchild Elements to my XML File in MS Access VBA

I had found some code online to create a XML file from VBA inside of MS Access. I am having trouble adding a GrandChild Element or another Child Element. Something like <Name> </Name>. This area is new to me in XML. Please help guide me in the right way. Thanks


Option Compare Database
Option Explicit

Private Sub cmdGo_Click()
Dim xml_doc As New DOMDocument
Dim employees_node As IXMLDOMElement

    ' Make the Employees root node.
    Set employees_node = xml_doc.createElement("Employees")
    xml_doc.appendChild employees_node
    employees_node.appendChild _
        xml_doc.createTextNode(vbCrLf)

    ' Add a comment.
    employees_node.appendChild xml_doc.createTextNode("  ")
    employees_node.appendChild xml_doc.createComment(" " & _
        "Employee Records")
    employees_node.appendChild _
        xml_doc.createTextNode(vbCrLf)

    ' Make some Employee elements.
    MakeEmployee employees_node, "Arthur", "Anderson", 1
    employees_node.appendChild xml_doc.createTextNode(vbCrLf)
    MakeEmployee employees_node, "Beatrice", "Baker", 22
    employees_node.appendChild xml_doc.createTextNode(vbCrLf)
 

    ' Write the document.
    xml_doc.Save ("C:\Users\Station\Documents\Access XML Save Files\Test14update2.xml")

    Debug.Print xml_doc.XML

    'MsgBox "Done"
End Sub
    
    
    
' Make an Employee element.
Private Sub MakeEmployee(ByVal parent_node As _
    IXMLDOMElement, ByVal first_name As String, ByVal _
    last_name As String, ByVal employee_id As Integer)
Dim employee_node As IXMLDOMElement
Dim first_name_node As IXMLDOMElement
Dim last_name_node As IXMLDOMElement

    ' Make the Employee element.
    Set employee_node = _
        parent_node.OwnerDocument.createElement("Employee")
    parent_node.appendChild employee_node

    ' Add the Id attribute.
    employee_node.setAttribute "Id", Format$(employee_id)

    ' Add the FirstName and LastName elements.
    Set first_name_node = _
        parent_node.OwnerDocument.createElement("FirstName")
    employee_node.appendChild first_name_node
    first_name_node.appendChild _
        parent_node.OwnerDocument.createTextNode(first_name)

    Set last_name_node = _
        parent_node.OwnerDocument.createElement("LastName")
    employee_node.appendChild last_name_node
    last_name_node.appendChild _
        parent_node.OwnerDocument.createTextNode(last_name)
End Sub

Open in new window



OUTPUT:
<Employees>
  <!-- Employee Records-->
<Employee Id="1"><FirstName>Arthur</FirstName><LastName>Anderson</LastName></Employee>
<Employee Id="22"><FirstName>Beatrice</FirstName><LastName>Baker</LastName></Employee>
</Employees>

Open in new window



Expected Output:
<Employees>
  <!-- Employee Records-->
 <Employee Id="1">
  <Name>
   <FirstName>Arthur</FirstName>
   <LastName>Anderson</LastName>
  </Name>
 </Employee>
 <Employee Id="22">
  <Name>
   <FirstName>Beatrice</FirstName>
   <LastName>Baker</LastName>
  </Name>
 </Employee>
</Employees>

Open in new window

XMLVBAMicrosoft Access

Avatar of undefined
Last Comment
Dustin Stanley

8/22/2022 - Mon