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
Microsoft AccessXMLVBA
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


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>


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>
ASKER CERTIFIED SOLUTION
zc2

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

Join our community to see this answer!
Unlock 2 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros