troubleshooting Question

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

Avatar of Dustin Stanley
Dustin Stanley asked on
XMLVBAMicrosoft Access
11 Comments2 Solutions1462 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
zc2

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 11 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 11 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004