We help IT Professionals succeed at work.

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

Dustin Stanley
on
1,386 Views
Last Modified: 2017-04-03
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

Comment
Watch Question

zc2
CERTIFIED EXPERT

Commented:
replace the lines 53 and down of  the MakeEmployee function with the following code which creates an  extra "Name" element node and inserts the grandchildren to it.

Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
    employee_node.appendChild name_node

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

    Set last_name_node = _
        parent_node.OwnerDocument.createElement("LastName")
    name_node.appendChild last_name_node
    last_name_node.appendChild _
        parent_node.OwnerDocument.createTextNode(last_name)

Open in new window

Dustin StanleyEntrepreneur

Author

Commented:
Thank you very much.  I have been online all day trying to study up on XML as much as possible to get his project done.

In the part of the code:
Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
    employee_node.appendChild name_node

Open in new window


If for some unknown reason I wanted to make Name a child of First Name What part would I change to make this happen.

I have tried
Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
    first_name_node.appendChild name_node

Open in new window

and
Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
   employee_node.first_name_node.appendChild name_node

Open in new window

But no success.
Again thanks a ton!
zc2
CERTIFIED EXPERT

Commented:
This one looks correct to me:
Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
    first_name_node.appendChild name_node

but this one is not correct:
Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
   employee_node.first_name_node.appendChild name_node

elements do not expose their children as COM object properties, so, there is no such property as employee_node.first_name_node even if the variable first_name_node representing an element which is a child of employee_node in the tree.
Dustin StanleyEntrepreneur

Author

Commented:
Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
    first_name_node.appendChild name_node

Open in new window


Just gives me 91 Error Object VCariable or With Block not set.
zc2
CERTIFIED EXPERT

Commented:
Please post your whole code and specify the line you having that error
Dustin StanleyEntrepreneur

Author

Commented:
Line 57 Just gives me 91 Error Object Variable or With Block not set.
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)

    Dim name_node As IXMLDOMElement
Set name_node = _
        parent_node.OwnerDocument.createElement("Name")
    first_name_node.appendChild name_node

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

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

Open in new window

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Dustin StanleyEntrepreneur

Author

Commented:
I thank you very much. I think I am confusing something here. What I want is:
<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


I am trying to figure which part of the code changes where the Element is placed. (Stepped Down Into) Like FirstName and LastName are inside Name.   So I can change things Like:

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

Open in new window


I know this may not make exact sense but this coding is just a small sample for me to learn so I can apply it to a larger one I have to do. i am just struggling with trying to figure out how to place the children under the correct parents. Thanks.
zc2
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Dustin StanleyEntrepreneur

Author

Commented:
Thanks. I had to leave work. I will try this in the morning. I hope tomorrow I can get it right. Thanks.
Dustin StanleyEntrepreneur

Author

Commented:
Thanks for the help!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.